title: Slow MySQL Query Over Millions of Records
body: <p>Experts,</p>
  
  <p>I need help with a MySQL query.  It involves two tables: 'usedcars' and 'counties'.</p>
  
  <p>'usedcars': 16+ million records,</p>
  
  <p>'counties': 3100 records</p>
  
  <p>My query takes <em>minutes</em> to complete.  It's been hard to troubleshoot because MySQL caches the result so when I rerun the same query it comes up in under a second!</p>
  
  <p>I'm sure the subquery is killing me so I could use some tips for an alternative.</p>
  
  <p>Here's a sample:</p>
  
  <pre><code>SELECT p.make,p.total,sum(p.total) AS theCount, 
  sum(p.total)/
      (SELECT sum(p.total) AS theCount FROM usedcars p, counties c
      WHERE c.FIPS IN (55009,55061,55083) AND c.statecounty = p.statecounty) AS MktPerc 
  FROM usedcars p, counties c 
  WHERE c.FIPS IN (55009,55061,55083) 
  AND c.statecounty = p.statecounty 
  GROUP BY make 
  ORDER BY theCount DESC
  </code></pre>
  
  <p>I'm looking for the number of cars sold by brand in specific counties (FIPS) and also the Market Share (mktPerc) of that brand in the same sounties. </p>
  
  <p>Market Share being: number-of-cars-sold-by-a-brand / number-of-cars-sold-by-ALL-brands</p>
  
  <p>Here's the EXPLAIN:</p>
  
  <p><a href="http://1drv.ms/NqVuYT" rel="nofollow">http://1drv.ms/NqVuYT</a></p>
  
  <p>Here is an example return.  It's giving me exactly what I want.  Just takes too long!</p>
  
  <p><a href="http://1drv.ms/1egJ0xm" rel="nofollow">http://1drv.ms/1egJ0xm</a></p>