Re: Different Servers Using Different Index for Same Query on Same Data

[Jørgen Løland]

Hi Shane,

MySQL uses index cardinality statistics to determine which (if any) index will be used to access the table. If the statistics are off, MySQL will not be able to make the right decision.

The statistics are refreshed when
* a table is opened (when the *server* opens it, so from a user's perspective this is not easy to determine)
* ANALYZE TABLE is executed (yes, this is sent to the slaves unless explicitly suppressed)
* When 1/16th of the table has changed

The problem you're facing is most likely that the statistics differ a lot between the servers. To be sure, you'll have to compare the cardinality provided by SHOW INDEXES on one server that does the right thing and one that does not.

The reason why index cardinality differs can either be that a) one of the servers refreshed the statistics recently but the other one did not, or b) that table analysis came up with a cardinality estimate far from the actual cardinality [1]

You say that the problem goes away by restarting the server, and this is probably because the table statistics are refreshed when the table is first opened after restart. ANALYZE TABLE should give the same effect.

[1] This can happen because statistics are gathered by doing eight random dives into each index, and if you're unlucky these dives enter index nodes that are not representative of the index (see http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html for more info).

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com