title: MySQL - SELECT + JOIN + ORDER BY performance
body: <p>I'm having two tables, I need to select some data joined from both of them</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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)</p>
  
  <p>Can anyone tell me how to speed up the query?</p>
  
  <p>Here's the result of EXPLAIN</p>
  
  <pre><code>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
  </code></pre>