Re: Identical servers different plan for the same query
Posted by:
Eric Coll
Date: April 09, 2012 10:24AM
I have first got to the bottom on the problem, and resolved it at least for the moment. However, I am concerned about the unpredictability of the server, and how a simple fast query can't result on an absurd plan and take hours.
Bottom of the problem
----------------------
- Both servers correctly estimated that using the account index requires to scan millions of rows.
- The master correctly figured out that using the unique index was faster, though I don't understand its estimate of "1" row
- The slave erratically estimated more rows access for using the unique index,
This is either a bug or a bad optimizer implementation. The slave is completely off.
Running Analyze table made no difference.
I wish I could attach the plans so they are nicely formatted, but it comes down to the following plans (table, index, rows)
Master:
last_timestamp, PRIMARY, 24
pool_usage, idx_usage_unique, 1
Forcing master plan on the slave
last_timestamp, PRIMARY, 24
pool_usage, idx_usage_unique, 10130832
Master plan is correct and run fast on both master and slave. The optimizer is not working well on the slave.