MySQL Forums :: Optimizer & Parser :: Different Servers Using Different Index for Same Query on Same Data


Advanced Search

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


Subject Views Written By Posted
Different Servers Using Different Index for Same Query on Same Data 3799 Shane Cruz 03/29/2011 08:58AM
Re: Different Servers Using Different Index for Same Query on Same Data 1388 Jørgen Løland 03/30/2011 01:42AM
Re: Different Servers Using Different Index for Same Query on Same Data 1015 Shane Cruz 03/30/2011 10:05AM
Re: Different Servers Using Different Index for Same Query on Same Data 1110 Rick James 03/31/2011 11:06PM
Re: Different Servers Using Different Index for Same Query on Same Data 980 Jørgen Løland 03/31/2011 11:52PM
Re: Different Servers Using Different Index for Same Query on Same Data 971 Rick James 04/01/2011 08:41AM
Re: Different Servers Using Different Index for Same Query on Same Data 806 Jørgen Løland 04/03/2011 12:27PM
Re: Different Servers Using Different Index for Same Query on Same Data 1132 Øystein Grøvlen 04/12/2011 03:49AM


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.