title: Why is this MySQL query slower on one server than another?
body: <p>I have an SQL query I'm trying to optimise. Interestingly, on one MySQL server, the query runs in around 0.06 seconds, which I'm happy with, but on another server, the query takes closer to 0.2 seconds (twice as long).</p>
  
  <pre><code>SELECT fo.facets_id, fo.facets_options_id, fo.value
  FROM facets_options fo
  INNER JOIN categories_facets cf ON cf.facets_options_id=fo.facets_options_id
  INNER JOIN categories c ON c.categories_id=cf.categories_id
  WHERE fo.facets_id="2"
  AND fo.language_id = "1"
  AND c.enabled=1
  GROUP BY fo.facets_options_id
  ORDER BY fo.value ASC;
  </code></pre>
  
  <p>The "EXPLAIN" output on both servers is identical:</p>
  
  <pre><code>id          select_type          table  type   possible_keys                                             key               key_len  ref                        rows Extra
  1           SIMPLE               fo     ref    PRIMARY,facets_options_id,facets_id,facets_id_2           facets_id         4        const                      986  Using where; Using temporary; Using filesort
  1           SIMPLE               cf     ref    PRIMARY,categories_id,categories_id_2,facets_options_id   facets_options_id 4        mydb.fo.facets_options_id  37   
  1           SIMPLE               c      eq_ref PRIMARY,enabled                                           PRIMARY           4        mydb.cf.categories_id      1    Using where
  </code></pre>
  
  <p>The server on which the query runs slowly is using MySQL 5.1.66, and the server on which it is fast is using MySQL 5.5.29.</p>
  
  <p>Is it likely that the newer version of MySQL is doing a better job of optimizing the query? Or is there something else that can be causing it? How can I find out more about what's going on behind the scenes?</p>