MySQL - SELECT + JOIN + ORDER BY performance

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 OR开发者_运维问答DER 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

---

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.

---

You should put an index on f.date

---

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