MySQL Forums
Forum List  »  Optimizer & Parser

Different Servers Using Different Index for Same Query on Same Data
Posted by: Shane Cruz
Date: March 29, 2011 08:58AM

We have our environment setup with a single master and several slaves all running MySQL 5.0.67. We occasionally see cases where the optimizer is using a different index on different servers and it causes severe performance problems. This typically happens on queries against pretty large tables (30+ million records) that are using the InnoDB engine. Oddly, when we restart the MySQL process on the servers using the wrong index, they typically start using the correct index again.

Is there anything we can do to proactively avoid this from happening? Should we be running an ANALYZE on these tables on a regular basis? I assume it doesn't make sense to have to proactively restart the MySQL processes on a scheduled basis or always use a FORCE INDEX in the SQL statement, but we cannot afford to have this keep happening.

Thanks for your help!

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.