MySQL Performance Example Was: Seriously, WTF?

[Tim Sweetman]

On 8 May 2008, at 16:22, Jonathan Stowe <jns at integration-house.com>  
wrote:

> On Thu, 2008-05-08 at 16:01 +0100, Christopher Jones wrote:
>> To reply to the first few questions, there isn't (currently) an index
>> on `location`. I did try using an index, but I'm pretty certain it
>> made the query slower (?!) so I removed it. But that just doesn't
>> seem right - so to make sure I've added the index back again and
>> (once MySQL is finished) I'll re-do the query and let you know what
>> happens.
>>
>
> I'm not sure about MySQL but elsewhere I'd be inclined to make a  
> single
> index on both 'location' and 'chr' (ie "CREATE INDEX
> histones_chr_location ON histones(chr, location)" ) which gives the
> optimizer a better hint that it should use that index for that kind of
> where clause.

Seconded. Such a compound index is ideal for this kind of query. I  
don't know whether this got amended somewhere in mysql5.x, but mysql  
used to be able to only use one index per query.

Also, your location range looks quite large: if the query is reading  
the whole table, an index will yield limited improvement, or none. You  
want an index matching your most selective WHERE clauses.

(That's assuming your data isn't being updated in real time or  
something, ruling out the expense of maintaining indexes).

HTH,

Tim


>
>
>
>> This is what's in there at the moment (please don't flame for use of
>> MyISAM - there are no foreign keys being used, so I didn't bother
>> setting an ENGINE, and it defaulted to MyISAM).
>>
>>
>> CREATE TABLE `histones` (
>>   `read_id` int(11) NOT NULL auto_increment,
>>   `chr` varchar(4) NOT NULL default '',
>>   `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
>>
>> ...
>>
>>
>>
>>
>>
>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>