title: MySQL Master and Slave with vastly different execution plans
body: <p>I have a complex MySQL query that joins three tables and self-joins one table to itself.</p>
  
  <p>There is a Master and a Slave that have identical data and indices.  The Master is a powerful box compared to the Slave, yet the query runs 10x faster on the Slave (during a period of light load for the Master).</p>
  
  <p>The execution plans are vastly different.</p>
  
  <pre><code>Master execution plan
  1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using temporary; Using filesort'
  1, 'SIMPLE', 'table2_', 'ref', 'PRIMARY,FK376E02E910238FCA', 'FK376E02E910238FCA', '13', 'const', 105, 100.00, 'Using where'
  1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F9398CD28D0', '13', 'table2_.ID', 1515, 100.00, 'Using where'
  1, 'SIMPLE', 'table1_', 'eq_ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'PRIMARY', '8', 'table0_.FK_ID', 1, 100.00, 'Using where'
  
  Slave execution plan
  1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using filesort'
  1, 'SIMPLE', 'table1_', 'ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'FKE7E81F1ED170D4C9', '9', 'const', 187398, 100.00, 'Using where'
  1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F937DD0DC02', '9', 'table1_.ID', 1, 100.00, 'Using where'
  1, 'SIMPLE', 'table2_', 'eq_ref', 'PRIMARY,FK376E02E910238FCA', 'PRIMARY', '12', 'table0_.FK_ID', 1, 100.00, 'Using where'
  </code></pre>
  
  <p>The tables are processed in different orders and the master DB uses both a temporary table and a filesort, while the slave uses only a filesort.</p>
  
  <p>What factors could cause the differing plans with such vastly different execution times?</p>
  
  <p>UPDATE:</p>
  
  <p>Is it possible this has to do with index statistics?  I plan to run an ANALYZE TABLE on the Master during a low-volume period. SHOW INDEX shows very different cardinality for some of the keys between Master and Slave.</p>