Skip navigation links

MySQL Forums :: Optimizer & Parser :: Identical servers different plan for the same query


Advanced Search

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


Subject Views Written By Posted
Identical servers different plan for the same query 1545 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 655 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 648 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 855 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 866 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 611 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 617 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 596 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 590 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 622 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 619 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 657 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 622 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 644 Rick James 04/13/2012 03:49PM


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.