2

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.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • This is a thread of same issue http://stackoverflow.com/questions/591210/mysql-explain-different-results-on-different-servers-same-query-same-db – Sanghyun Lee Feb 01 '13 at 02:21

3 Answers3

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

MindStalker
  • 14,629
  • 3
  • 26
  • 19
  • The actual statistics will change over time so I would rather not provide a key hint. I'm going to try an ANALYZE TABLE tonight to rebuild statistics and see if this helps. If so, I'll add that to the routine maintenance plan for the DB. – Eric J. Dec 15 '09 at 19:53
  • 1
    After running ANALYZE TABLE on the relevant tables in the master, I got the same execution plan and comparable execution time as on the slave. I'm going to select this answer because it was closest regarding the collected statistics but I did not specify a key in the query and don't think that's a good idea in this case. – Eric J. Dec 16 '09 at 05:20
0

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

Are both servers on the same version of MySQL?

Ben S
  • 68,394
  • 30
  • 171
  • 212
  • Both are using the exact same release of MySQL, though the Master is using the 64-bit version while the slave is using the 32-bit one. – Eric J. Dec 15 '09 at 19:35
  • "just different plan" is not a bug – noonex Dec 15 '09 at 19:56
  • A different plan which results in a slower execution on faster hardware. That sounds like a performance bug to me. – Ben S Dec 16 '09 at 01:39
  • The problem was that the index statistics on the Master were not very accurate. The Slave had fairly accurate statistics because it was connected to the Master fairly recently and so created the index statistics with much more data to go on. – Eric J. Dec 16 '09 at 17:10
0

SHOW INDEX shows very different cardinality for some of the keys between Master and Slave.

I met the same issue, and I found the reason was it: different cardinality. And then I ran analysis table, the cardinality were same and the problem had gone.

ytll21
  • 850
  • 6
  • 12