MySQL Performance Example Was: Seriously, WTF?
davehodg at gmail.com
Fri May 9 09:09:21 BST 2008
On 9 May 2008, at 09:25, Andy Wardley wrote:
> 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.
Making the whole table fixed width, which your suggestion would do,
is also a Very Big Win.
>> `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.
And depending on the number of rows, making 'histone' char() would
be the same win as above.
> 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.
Dave Hodgkinson MSN: davehodg at hotmail.com
Site: http://www.davehodgkinson.com UK: +44 7768 49020
Blog: http://davehodg.blogspot.com NL: +31 654 982906
More information about the london.pm