MySQL Forums
Forum List  »  Optimizer & Parser

Re: Identical servers different plan for the same query
Posted by: Eric Coll
Date: April 09, 2012 10:35AM

The solution:
- I added an index by timestamp, region, account

Here is the list of all the problems that I found:
- After rebooting the master, the master switched to a plan as bad as the slave
- Creating the index took over 7 hours. This is more time than backing all the data recreate the table and load the data.
- I shut down the server the first time thinking the index creation has failed. After restarting the server I couldn't create the index with the same name, and I couldn't access the failed index either to drop it or repair it.
- After creating the index the master started using it correctly
- The slave kept using the bad query plan even after a reboot! Shouldn't it update the statistics? Running analyze table solved the problem
- After a while, the master switched back to its old plan using the unique index, not the new one that I created.

*Note: this is an existing very large live database, and several features internal and external run against it, so changes must be done with care. We have an identical gamma version for development, but the issue doesn't repro in that environment.

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.