Re: Identical servers different plan for the same query
Posted by:
Rick James
Date: April 08, 2012 12:20PM
> Shouldn't both servers use the same query plan?
I have seen as many as 6 different query plans for similar queries, differing only in the constants used. Even with identical constants, the query plan can vary even on the same machine.
InnoDB does 8 random dives into the indexes when the table is first opened, and at certain other times. From this it deduces the statistics which it uses to decide on the 'best' plan.
The Master and Slave do not necessarily have all the rows in exactly the same blocks. This does not impact the results of identical queries, but could impact the statistics.
ANALYZE TABLE is one of the things that will cause InnoDB to recompute the statistics -- for better or worse.
UNIQUE (`timestamp`,`zone`,`pool_type_id`,`week`,`account`,`instance_class`,`operation`)
Beware -- we are likely to jump on how awful that index looks.
on pu.region = lt.region and
pu.timestamp = lt.timestamp and
sounds unsafe -- unless you are absolutely sure no two things can happen at exactly the same second.