MySQL Performance Example Was: Seriously, WTF?

[Dave Hodgkinson]

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.
>
> HTH
> A
>
>

-- 
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
Photos: http://www.flickr.com/photos/davehodg