Re: Identical servers different plan for the same query

[Eric Coll]

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.