MySQL Master and Slave with vastly different execution plans

Question

I have a complex MySQL query that joins three tables and self-joins one table to itself.

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).

The execution plans are vastly different.

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'

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.

What factors could cause the differing plans with such vastly different execution times?

UPDATE:

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.

Answer 1

This looks like a bug in the query optimizer to me. I would report it.

Are both servers on the same version of MySQL?

Answer 2

MySQL optimizes queries based upon collected statistics.

Looking at your output you see that they are using different keys, you might have to add key hints or even force keys

FROM table2_ JOIN

should become

FROM table2_ USE KEY('FK376E02E910238FCA') JOIN

Or FORCE KEY