Posted by Vadim |

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB:
It is

  • INNODB_TABLE_STATS
  • INNODB_INDEX_STATS

These table show statistics about InnoDB tables ( taken from InnoDB data dictionary).

INNODB_TABLE_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | rows | estimated number of all rows |
  • | clust_size | cluster index (table/primary key) size in number of pages|
  • | other_size | other index (non primary key) size in number of pages|
  • | modified | internal counter to judge whether statistics recalculation should be done |

INNODB_INDEX_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | index_name | index name |
  • | fields | How many fields the index key has. (it is internal structure of InnoDB, it may be larger than the ‘CREATE TABLE’) |
  • | row_per_keys | estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], …) |
  • | index_size | index size in pages |
  • | leaf_pages | number of leaf pages |

Using these stats you can estimate how big is index is, and also what is statistics per index (or at least what InnoDB thinks about statistics in index)