MySQL - SELECT + JOIN + ORDER BY performance

Question

I'm having two tables, I need to select some data joined from both of them

SELECT f.* 
FROM file_data f
JOIN subscriptions s ON f.uid = s.elementid
WHERE s.uid = 119762 AND f.private=0  
ORDER BY f.date DESC

Now, even for a small set of data the query takes over a second. This is due to 'filesort' and 'temporary' used on "subscriptions", caused by ORDER BY f.date (removing that condition causes the time to drop under 0.01s)

Can anyone tell me how to speed up the query?

Here's the result of EXPLAIN

id  select_type    table    type   possible_keys    key            key_len  ref           rows  Extra
1    SIMPLE         s        ref    uid_elementid    uid_elementid  4        const         171   Using index; Using temporary; Using filesort
1    SIMPLE         f        ref    uid_uname        uid_uname      5        s.elementid   22    Using where

Answer 1

You should put an index on f.date

Answer 2

When an index will help an order by is non-trivial to predict. It is very rarely as simple as "index the fields in the order by". This link has the nitty-gritty details. If you think you have proper indexing and the optimizer just isn't deciding to use them, use an index hint to suggest the index to the database server.

Answer 3

I met the same problem,this is my solution:

SELECT f.* 
FROM (SELECT * FROM file_data WHERE `private`=0 ORDER BY `date` DESC ) f
JOIN subscriptions s ON f.uid = s.elementid
WHERE s.uid = 119762