title: MySQL ORDER BY - Slowing Down Query
body: <p>Question pertains to a listing site that holds listing data and lead data in two tables.  When doing a query for listings and total number of leads for each listing, the results are extremely slow after adding an ORDER BY. Without the ORDER BY, the results are retrieved very fast.  Any advice or help with restructuring the query below would be awesome!! Fyi, there are 20k listings and 100k leads.</p>
  
  <pre><code>SELECT ls.*, IFNULL(ld.total_leads, 0) AS total_leads
  FROM listing ls 
  LEFT JOIN (SELECT listing_id, COUNT(listing_id) AS total_leads 
              FROM lead GROUP BY listing_id) ld
  ON (ls.listing_id = ld.listing_id)
  ORDER BY ls.listing_id DESC LIMIT 0,20
  </code></pre>
  
  <p>EXPLAIN:</p>
  
  <pre><code>id select_type table type pos key ke ref rows Extra 
  1 PRIMARY ls ALL NULL NULL NULL NULL 17215 Using temporary; Using filesort 
  1 PRIMARY 2&gt; ALL NULL NULL NULL NULL 12865 
  2 DERIVED lead ALL NULL NULL NULL NULL 117830 Using temporary; Using filesort
  </code></pre>