[SOLVED] ORDER BY killing

[jaymc]

I have a simple query like so

 

SELECT f.user, f.friend, f.gender, f.x, f.timestamp, c.propic as image

FROM friends f

INNER JOIN cache c ON f.friend = c.username

WHERE f.user = 'jaymc' AND f.gender = 'Male'

ORDER BY f.x DESC, f.timestamp DESC LIMIT 0,10

 

That query takes 100 seconds on a table with 5 million rows

 

If I take the order clause out, it takes under 1 second

 

I really need the order clause, will adding an index on both help? Do I just need to add an index on the f.x field?

 

Please advise

[Mchl]

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

[jaymc]

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,f,ref,user,friend,user,27,const,17864,Using where; Using filesort

1,SIMPLE,c,eq_ref,PRIMARY,PRIMARY,27,db_name.f.friend,1,

[Mchl]

If you want to make your queries as fast as possible' date=' you should look out for Extra values of [b']Using filesort[/b] and Using temporary.

 

 

* Using filesort

 

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 7.2.12, “ORDER BY Optimization”.

[jaymc]

I dont quite follow

 

What are you saying is the solution?

[corbin]

Do you have an index on friends.x?

[Mchl]

Did you read section 7.2.13 of mySQL docs? There are examples of queries with ORDR BY clause that can, and that cannot benefit from adding indexes. Try to figure out, how to modify your query, and what index to add, so that it was performing better.

[jaymc]

A convering index on the 5 fields in this query resovled the issue

 

It is still using file sort, but must be only mildly as the query takes less than one second, not 100 seconds+

 

Thanks

[fenway]

You could cheat by adding the covering index with the order-by'ed columns first... I think I've seen this work before, since the index itself is sorted... not sure how mysql figures this out, but I think it might work