MySQL Forums
Forum List  »  MyISAM

Improve performance of a query with join and order by
Posted by: Mario Rossi
Date: July 16, 2012 03:25AM

Hi all, i have the following situation:

First table is table1(field1,field2,field3,.......,field11,field12) and second table is table2(a, b, c)

a is foreign key for field1 which is an integer.

field12 is a timestamp and it has an index on it.

table1 has nearly 2000000 records and table2 has nearly 56000000 records and both tables are MyISAM.

I have to perform the following query:

FROM table1 t1
JOIN table2 t2 ON t2.a = t1.field1
AND t2.b = '83'
AND t2.c = '66'
WHERE t1.field3 IN ( 6 )
AND t1.field4 = '0'
ORDER BY t1.field12
LIMIT 5000

The problem is that it takes nearly 12 minutes to execute and i don't get why.
Expecially i don't get why the index can't help inside the ORDER BY.

I tried to execute it with explain and i can see that it's not using the index on field12.

Can someone tell me what am i missing?

Thanks in advance for the help.

Options: ReplyQuote

Written By
Improve performance of a query with join and order by
July 16, 2012 03:25AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.