MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.