Fixing Slow MySQL SELECT Query That Contains “ORDER BY”
19 Wednesday Nov 2008
Written by amri in Computer Thingy, Database, MySQL
Last night I was dealing with a very slow SELECT query with MySQL database.
The query itseft is a SELECT of multiple table (using JOIN) and has an "ORDER BY" clause.
Lets say that the table name is X, A, B, C, and D.
Table X contains foreign keys that linked to table A, B, C, and D (lets just say that table X contains column A, B, C, D, for the foreign key and column E, F, and G)
I tried to remove the "ORDER BY", and the query is run amazingly fast !
That made me think "this could be related to the index or primary key"
So, i check the primary key on table X.
Haha! Immediately i found the problem.
The sequence of primary key indexing was different than the sequence of the query !
For example in this case :
Index sequence of table X primary key is C, D, B, A and PK name is PK_X
ORDER BY sequence is table X column A, B, D
Here are some options to speed up the query :
Use "IGNORE KEY(PK_X)" after the FROM X clause
(i.e. SELECT ....... FROM X IGNORE KEY(PK_X) JOIN A ..........)
The query speed up but still a bit slow.
Remove column "C" from PK_X
Somewhat faster query time that the "IGNORE KEY" fix, but i dont think this is the correct approach.
Rearrange the PK_X sequence to A,B,C,D
Waaaay faster that other fix, so i choose this approach.
Just for reference, before applying this fix the query time is about 6 second
With the first option it speed up to 3second.
With the second option it speed up to 2.1second.
And with the last option i get incredible 0.03 second.