Comment 3 for bug 1538765

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :

Analysis.

InnoDB stores persistent statistics and does not update it.

Optimizer does not use InnoDB statistics directly, but via class ha_statistics (sql/handler.h) which does not have any member to represent Cardinality. Instead it has member rec_per_key: number of repeated values in index in time when ANALYZE was running last time.

When InnoDB opens table first time it reads its persistent statistics using function ha_innodbase::info with flag HA_STATUS_CONST and stores it in memory. All subsequent table accesses call ha_innodbase::info with different flag: HA_STATUS_VARIABLE. Therefore function execution goes to another branch. In this branch in-memory data is used and also few stats are updated, particularly number of rows in the table (which is pretty accurate).

Optimizer, in its turn, uses number of rows in table (constantly changing) and divides it on rec_per_key value (persistent). Thus we see different cardinality after few updates.