title: ORDER BY on timestamp field makes my query slow. How to optimize it?
body: <p>I'm working on a MySQL database that stores "Facebook-like" images: every user follows a set of "artists", and these artists upload images.</p>
  
  <p>I wrote a query that, for a given user, returns the images of all the artists he follows, that are more recent than 30 days, sorted by date:</p>
  
  <pre><code>SELECT img.id, img.url, a.name
  
  FROM (SELECT artist FROM user_follow_artist WHERE user = &lt;USER_ID&gt;) AS f -- need to work only on the followed artists
  JOIN artistimage AS img ON img.artist = f.artist -- join on the table that contains the images (8.000.000 rows!)
  JOIN artist AS a ON a.id = img.artist -- join on artist table to add details on image's owner
  
  -- following: where + orderby that slow down everything
  WHERE img.uploadDate &gt; NOW() - INTERVAL 30 DAY 
  ORDER BY img.uploadDate DESC 
  LIMIT 50
  </code></pre>
  
  <p>The query tooks an average of 30 seconds, the more the user follows artists, the more it takes.
  Here are some details:</p>
  
  <ul>
  <li>As written, the artistimage table is 8 millions rows big</li>
  <li>If I remove the ORDER BY clause, the time drops to 2-3 seconds</li>
  <li>img.uploadDate is a TIMESTAMP and it's INDEXED</li>
  <li>The engine is InnoDB</li>
  </ul>
  
  <p>I hoped indexing uploadDate could increase the speed, but nothing changed. How can I solve this problem?</p>
  
  <p>EDIT: Table structures</p>
  
  <pre><code>artist
  ------------------
  id (integer, primary)
  name (string)
  
  user_follow_artist
  ------------------
  user (integer, foreign key on user.id, indexed)
  artist (integer, foreign key on artist.id, indexed)
  
  artistimage
  ------------------
  id (integer, primary)
  artist (integer, foreign key on artist.id, indexed)
  url (string)
  uploadDate (timestamp, indexed)
  </code></pre>
  
  <p>EXPLAIN:</p>
  
  <pre><code>id      select_type     table               type        possible_keys       key         key_len     ref             rows    Extra
  1       PRIMARY         &lt;derived2&gt;          ALL         NULL                NULL        NULL        NULL            327     Using temporary; Using filesort
  1       PRIMARY         img                 ref         artist,uploadDate   artist      9           img.artist      36      Using where
  1       PRIMARY         user                eq_ref      PRIMARY             PRIMARY     8           db.img.artist   1    
  2       DERIVED         user_follow_artist  ref         PRIMARY,user        user        8                           327     Using index
  </code></pre>
  
  <p>If I remove the ORDER BY, the EXPLAIN is the same but without <code>Using temporary; Using filesort</code> in first row.</p>