title: Modifying a MySQL Query to Stop Writing Temporary Table to Disk
body: <p>We are improving some of our SQL queries around the site and have noticed a bottleneck with a particular SQL query that seems to dump the results into a temporary disk table.  This is expensive and of course awfully slow.</p>
  
  <p>We have considered moving the MySQL temporary directory over to a RAMDISK but thought we could try to optimize it first.</p>
  
  <p><strong>Here is the command that dumps to disk:</strong></p>
  
  <pre><code>select
    d.url,
    d.lid,
    d.title, 
    d.description, 
    d.date, 
    d.hits, 
    d.downloadratingsummary, 
    d.totalvotes, 
    d.totalcomments, 
    d.filesize, 
    d.version, 
    d.homepage, 
    d.ns_compat, 
    d.ns_des_img, 
    t.type
  from downloads_downloads d
  LEFT JOIN downloads_type t on d.lid = t.lid
  where cid=91
  ORDER BY FIELD(COALESCE(t.type,-1),1,-1,2,4,5,0,3), date DESC limit 0,20
  </code></pre>
  
  <p>Is there any simple way to reorganize/modify the above query to minimize temporary table creations on disk?</p>
  
  <p>While we have experience with MySQL, performance optimization is still something new to us so any help anyone can give is always greatly appreciated. </p>
  
  <p>Thanks for any help in advance.</p>
  
  <h1>Structure of both referenced tables</h1>
  
  <p><strong>downloads_downloads</strong></p>
  
  <p><img src="https://i.stack.imgur.com/8Xknz.png" alt="downloads_downloads table structure"></p>
  
  <p>Indexes</p>
  
  <p><img src="https://i.stack.imgur.com/KFGGv.png" alt="indexes"></p>
  
  <p><strong>downloads_type</strong></p>
  
  <p><img src="https://i.stack.imgur.com/WDiz7.png" alt="downloads_type table structure"></p>
  
  <p>Indexes</p>
  
  <p><img src="https://i.stack.imgur.com/d13Bd.png" alt="indexes2"></p>
  
  <h1>Output of EXPLAIN</h1>
  
  <p><img src="https://i.stack.imgur.com/jyqZY.png" alt="EXPLAIN output"></p>
  
  <h2>Environment:</h2>
  
  <p>OS X running MySQL 5.5.23 with 16GB RAM. </p>