Super slow query! Requesting suggestions!

[enoch]

Hello and thank you kindly for taking a look at this with me.

Your time is greatly appreciated. I am having a great deal of trouble with a query that is running super slow. Details follow:

Just the facts:

1)I wrote a php pagination function to which I pass a query that generates about 5500 results and displays it in pages of 25.

2) Showing rows 0 - 25 (5,500 total, Query <b>took 23.4910 sec</b>)

 

3) When I remove the ORDER BY the query takes less than one second.

 

4) I am using ORDER BY a datetime field to enable me to display results by the most recently accesed record(to the second). I need to maintain this feature.

 

5) Indexed, the datetime field contains the same number as the records returned.

 

Comments:

I am using InnoDB.

As the query results are displayed in pages - each new page takes roughly the same amount of time to load.

I am ok at php/mysql but not great (obviously).

 

The query: (names have been changed to protect the guilty)

SELECT t1.field, t1.field2, t2.field,

t1.field3, t1.field4, t3.field,

CONCAT(t4.field1,' ', t4.field2) AS somename,

t1.field5, t5.field, t6.field, t2.field2,

t2.field3, t7.field, t1.field6

FROM table1 AS t1

LEFT JOIN table2 AS t2 ON t1.field=t2.field

LEFT JOIN table3 AS t3 ON t1.field2=t3.field

LEFT JOIN table4 AS t4 ON t1.field3_id=t4.field

LEFT JOIN table5 AS t5 ON t1.field4_id=t5.field

LEFT JOIN table6 AS t6 ON t1.field5_id=t6.field

LEFT JOIN table7 AS t7 ON t1.field6=t7.field

ORDER BY t2.field2 DESC

 

EXPLAIN says:

1 SIMPLE t1 ALL NULL NULL NULL NULL 6012 Using temporary; Using filesort

1 SIMPLE t2 ALL NULL NULL NULL NULL 6012

1 SIMPLE t3 eq_ref PRIMARY PRIMARY 1 t3.table3 1

1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 t4.table4 1

1 SIMPLE t5 eq_ref PRIMARY PRIMARY 1 t5.table5 1

1 SIMPLE t6 eq_ref PRIMARY PRIMARY 1 t6.table6 1

1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 t7.table7 1

 

 

[fenway]

Two things:

 

1) there doesn't appear to be any useful index for table1 or table2

2) the temp/filesort comes from the fact that you're sorting on a join-ed table.