Improve performance of a query with join and order by

[Mario Rossi]

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:

SELECT t1.*
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.