Hi , i am running into trouble due to wrong index chosen by mysql in some particular type of queries . This is happening in a critical production environment where we have deployment in two different colocations . I am seeing that a paticular query is using one index in one set of hosts & another index in another set of hosts . We are not using 'use index' clause to explicitly mention the index due to some limitations . But wondering based on what mysql is using different indexes in different hosts . This is really surprising , since dataset & table structures are exactly same in all the hosts .
Other than changing the code to force using 'use index' , is there any other way to resolve it ? And what's the exact reason behind this ? Just to have mysql choose the correct index always will simply solve my problem . Thanks .
