title: MySQL Erratic Query Times
body: <p>I am using MySQL version 5.5.14 to run the following query from a table of 5 Million rows:</p>
  
  <pre><code>SELECT P.ID, P.Type, P.Name, P.cty
       , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
       , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
       , P.lv, P.bd, P.bt, P.nb
       , P.ak * E.usD as 'usP' 
  FROM PIG P 
    INNER JOIN EEL E 
      ON E.cur = P.cur 
  WHERE act='1' 
    AND flA &gt;= '1615' 
    AND ldA &gt;= '0' 
    AND yr &gt;= (YEAR(NOW()) - 100) 
    AND lv &gt;= '0' 
    AND bd &gt;= '3' 
    AND bt &gt;= '2' 
    AND nb &lt;= '5' 
    AND cDate &gt;= NOW() 
    AND MBRContains(LineString( Point(-65.6583, -87.8906)
                              , Point(65.6583, 87.8906)
                              ), latlng) 
    AND Type = 'g' 
    AND tn = 'l' 
    AND St + Tm - YEAR(NOW()) &gt;= '30' 
  HAVING usP BETWEEN 300/2 AND 300 LIMIT 100;
  </code></pre>
  
  <p><strong>On the first occasion, it took 313s, on the second occasion it took 48s and on the third occasion it took 101s.</strong> From explain select, the query plan has type: range; key: index and extra: using where on table P followed by type: eq_ref and key: primary on table E. This query does not use query cache. My question is: <strong>"Why is the query time so dramatically different?"</strong></p>
  
  <p>Cheers,
  Ben</p>