InnoDB Tables and Indexes Statistics

More often than not I've thought about the meaning of InnoDB statistics and how it works.

Every time I executed an 'ANALYZE TABLE' command the statistics related to the table were different and what's more nobody had modified the data of the table before.

It is odd, isn't it ?

So I decided to get down to the problem and following you'll find the first conclusion.


The innoDB statistics read only the first 8 pages of the subject. The other pages are only estimated. It looks like a bet, doesn't it ? 
You can change the amount of the initial pages to be loaded modifying the following parameter 'innodb_stats_sample_pages'. 
 The innoDB statistics are loaded into the memory every time:

1.  The table is read,
2. The command 'ANALYZE TABLE' is executed,
3. New records are loaded in the table,
4. The command 'SHOW TABLE/INDEX' is executed.

Frankly speaking I prefer the myISAM statistics. Those statistics are always complete. The table is completely read and what's more the related statistics are saved into the disk.

This is only a quick view of the statistics according to mysql. Lots of googling should be done to complete the task.

See you in my next blog.

Byeeeee