MySQL Performance Example Was: Seriously, WTF?
iainspeed at gmail.com
Thu May 8 19:39:51 BST 2008
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
> 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.
Troll-like non-troll mySQL hater (but well reasoned hate;)
More information about the london.pm