Tags

Share it

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.