MySQL Forums
Forum List  »  Optimizer & Parser

Re: Different Servers Using Different Index for Same Query on Same Data
Posted by: Jørgen Løland
Date: March 30, 2011 01:42AM

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 for more info).

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle

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.