MySQL Performance Example Was: Seriously, WTF?

[Andy Wardley]

Christopher Jones wrote:
> CREATE TABLE `histones` (
>   `read_id` int(11) NOT NULL auto_increment,
>   `chr` varchar(4) NOT NULL default '',

You'll get better performance if you make this a char rather than
varchar.  Fixed width is usually faster. Even better would be to
make it an int, although for single characters that probably won't
buy you much.

>   `location` int(10) unsigned NOT NULL default '0',
>   `tally` int(10) unsigned NOT NULL default '0',
>   `histone` varchar(20) default NULL,
>   PRIMARY KEY  (`read_id`),
>   KEY `chr` (`chr`),
>   KEY `histone` (`histone`)
> ) ENGINE=MyISAM AUTO_INCREMENT=99694252 DEFAULT CHARSET=latin1

You might want to put an index on the location field, too.

Another optimisation would be to pre-compute location ranges (if
that makes sense for your data).  e.g. add a 'locrange' field which
is 1 if location > 10000 and location < 20000, or 2 if > 20000 and
< 30000, and so on.

Then instead of selecting where location > 10000 and location < 20000
you would select where locrange=1.  It's denormalisation Jim, but not
as we know it.

HTH
A