MySQL Performance Example Was: Seriously, WTF?
abw at wardley.org
Fri May 9 08:25:44 BST 2008
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.
More information about the london.pm