When (not) to group indexes: multi-column primary indexes

MySQL & MariaDB Other MySQL® Questions
1

I am currently deciding whether I should group multiple-column primary indexes or not. I have to extend an existing database structure to support an additional hierarchical level. As it already has multi-column primary indexes with 4 columns, I am wondering if it is a good idea to increase this to 5. The current structure is:

CREATE TABLE fcg_game (
gid int(10) unsigned NOT NULL auto_increment,
uid int(10) unsigned NOT NULL default ‘0’,
funds int(10) unsigned NOT NULL default ‘0’,
start int(11) unsigned NOT NULL default ‘0’,
end int(11) unsigned NOT NULL default ‘0’,
game_key varchar(64) default NULL,
PRIMARY KEY (gid),
KEY leader_id (uid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_game_task (
gid int(10) unsigned NOT NULL default ‘0’,
tid int(10) unsigned NOT NULL default ‘0’,
position tinyint(2) unsigned NOT NULL default ‘0’,
start int(11) unsigned NOT NULL default ‘0’,
end int(11) unsigned NOT NULL default ‘0’,
PRIMARY KEY (gid,tid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_question (
qid int(10) unsigned NOT NULL auto_increment,
tid int(10) unsigned NOT NULL default ‘0’,
question varchar(255) NOT NULL default ‘’,
answers varchar(255) character set latin1 NOT NULL default ‘’,
factors varchar(255) character set latin1 NOT NULL default ‘’,
PRIMARY KEY (qid),
KEY tid (tid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_task (
tid int(10) unsigned NOT NULL auto_increment,
type set(‘action’,‘debriefing’,‘start’) NOT NULL default ‘action’,
title varchar(255) NOT NULL default ‘’,
description text NOT NULL,
self_education text NOT NULL,
calculation_factor int(10) unsigned NOT NULL default ‘1’,
bets varchar(255) NOT NULL default ‘’,
mail text NOT NULL,
PRIMARY KEY (tid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_user (
uid int(10) unsigned NOT NULL default ‘0’,
first_name varchar(64) NOT NULL default ‘’,
sur_name varchar(64) NOT NULL default ‘’,
province varchar(64) NOT NULL default ‘’,
office varchar(64) NOT NULL default ‘’,
first_login tinyint(1) unsigned default ‘0’,
PRIMARY KEY (uid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_user_game (
uid int(10) unsigned NOT NULL default ‘0’,
gid int(10) unsigned NOT NULL default ‘0’,
score_total int(10) unsigned NOT NULL default ‘0’,
score_last int(10) unsigned NOT NULL default ‘0’,
score_last_tid int(10) unsigned NOT NULL default ‘0’,
trainee_first_name varchar(64) NOT NULL default ‘’,
trainee_sur_name varchar(64) NOT NULL default ‘’,
trainee_mail varchar(64) NOT NULL default ‘’,
PRIMARY KEY (uid,gid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_user_question (
uid int(10) unsigned NOT NULL default ‘0’,
gid int(10) unsigned NOT NULL default ‘0’,
tid int(10) unsigned NOT NULL default ‘0’,
qid int(10) unsigned NOT NULL default ‘0’,
answer varchar(255) NOT NULL default ‘’,
factor float NOT NULL default ‘0’,
PRIMARY KEY (uid,gid,tid,qid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE fcg_user_task (
uid int(10) unsigned NOT NULL default ‘0’,
gid int(10) unsigned NOT NULL default ‘0’,
tid int(10) unsigned NOT NULL default ‘0’,
bet int(10) unsigned NOT NULL default ‘0’,
completed int(1) unsigned NOT NULL default ‘0’,
question_key varchar(32) default NULL,
PRIMARY KEY (uid,gid,tid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What I need to do now, is to extend the ‘fcg_user_game’ table with a new column, named ‘trid’ to support multiple ‘trainees’. I could simply add another column, named ‘trid’ and make that part of the primary key index. This means however that the tables ‘fcg_user_question’ and ‘fcg_user_task’ require and additional column for their primary key, named ‘trid’, bringing the number to respectively to 5 and 4 multi-column primary indexes.

Another option could be to create a unique (autoincrement) index for ‘fcg_user_game’ and replace the ‘uid’ and ‘gid’ columns with this index in the ‘fcg_user_question’ and ‘fcg_user_task’ tables, bringing down the number of columns in the primary index to 3 and 2.

My question now, what is the best approach? Is it harmfull to have 5 columns as primary index? Most of the queries will be pretty straightforward and often all of the values in the columns that make up the primary keys will be known. However, it is hard to foresee what other type of queries will need to be available in the future, so I would like to go with the best approach right now.

Thanks for looking at this and all feedback is very much appreciated.

2

As it is MyISAM you might do well with auto_increment key,
for Innodb combined key could be better because of clustering.

Also a lot depends on kind of lookups you’re going to do - if you
will need to have combined key anyway why to add artificial PK ?

3

ting at mysqlperformanceblog.com For an unknown reason this subject becomes blank…
Was it off topic ? If so, just tell me.

Anyway, if someone is looking for such a solution, here is mine (at least for now) :

2 data sources : one “read only” and the second one read/write.

  • The read only data source uses the MySQL ReplicationDriver, to load balance queries between the slaves

  • The R/W DS uses the MySQL fail over feature in the default Driver impl. That way, it’s possib