MySQL Performance Example Was: Seriously, WTF?

[Iain Barnett]

On 8 May 2008, at 4:01 pm, 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.

My tip for the day is to put DDL statements run against your db into  
a text file and stick it in source control, preferably with a comment  
about why it was done. Someday you will thank yourself for doing it.


> | chr      | varchar(4)

If you want a good, fast index, then make it fixed width (not  
var...), numeric (i.e. int, not a char holding an int as a string),  
and with lots of unique values. Why is chr a varchar?

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

Thirded - but I'd change it to "CREATE INDEX histones_chr_location ON  
histones( location, chr)" so that the more unique (looking at the  
exampe data) and numeric type value is on the left of the index as  
this will make it faster.


Iain
Troll-like non-troll mySQL hater (but well reasoned hate;)