Different Servers Using Different Index for Same Query on Same Data

[Shane Cruz]

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!