innodb_index_rows
NAME
innodb_index_rows: Number of row cardinality per keys per columns in InnoDB tablesTYPE
ViewDESCRIPTION
innodb_index_rows extends the INNODB_INDEX_STATS patch in Percona Server, and presents with information on InnoDB keys cardinality as per indexed column.
The Percona Server INNODB_INDEX_STATS table presents with cardinality values per index, per indexed column. That is, it lets us know the average number of rows expected to be found in an index for some key.
For single column keys, this is simple enough. However, for compound indexes (indexes over multiple columns), this information becomes even more interesting, as it lets us examine the reduction in cardinality (and improvement of selectivity) per column. However, the Percona Server patch only informs us with numbers.
innodb_index_rows extends that information and adds column information. It lists column names and their sequence within the key, and provides with easier to read format.
This view depends upon the INNODB_INDEX_STATS patch in Percona Server.
Note that Percona Server 5.5.8-20.0 version introduced changes to the INNODB_INDEX_STATS schema. This view is compatible with the new schema, and is incompatible with older releases.
STRUCTURE
mysql> DESC common_schema.innodb_index_rows; +-------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+---------------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | INDEX_NAME | varchar(64) | NO | | | | | SEQ_IN_INDEX | bigint(2) | NO | | 0 | | | COLUMN_NAME | varchar(64) | NO | | | | | is_last_column_in_index | int(1) | NO | | 0 | | | incremental_row_per_key | bigint(67) unsigned | YES | | NULL | | +-------------------------+---------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: Table schema of examined index
- TABLE_NAME: Examined index' table
- INDEX_NAME: name of index examined
- SEQ_IN_INDEX: position of column within index (1 based)
- COLUMN_NAME: name of column within index
- is_last_column_in_index: boolean, 1 if current column is last in index definition.
The last column in an index is of particular interest since the number of rows per last column signifies the index's maximum selectivity - incremental_row_per_key: Cardinality (number of values per key) of index up to and including current column
EXAMPLES
Examine index cardinality on a specific table (discussion follows):
mysql> SELECT * FROM common_schema.innodb_index_rows WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='inventory'; +--------------+------------+----------------------+--------------+--------------+-------------------------+-------------------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | is_last_column_in_index | incremental_row_per_key | +--------------+------------+----------------------+--------------+--------------+-------------------------+-------------------------+ | sakila | inventory | PRIMARY | 1 | inventory_id | 1 | 1 | | sakila | inventory | idx_fk_film_id | 1 | film_id | 1 | 5 | | sakila | inventory | idx_store_id_film_id | 1 | store_id | 0 | 4478 | | sakila | inventory | idx_store_id_film_id | 2 | film_id | 1 | 2 | +--------------+------------+----------------------+--------------+--------------+-------------------------+-------------------------+
Compare with call to INFORMATION_SCHEMA.INNODB_INDEX_STATS:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEX_STATS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='inventory'; +--------------+------------+----------------------+--------+--------------+-------------------+------------------+ | table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages | +--------------+------------+----------------------+--------+--------------+-------------------+------------------+ | sakila | inventory | PRIMARY | 1 | 1 | 10 | 9 | | sakila | inventory | idx_fk_film_id | 2 | 5, 1 | 5 | 4 | | sakila | inventory | idx_store_id_film_id | 3 | 4478, 2, 0 | 7 | 6 | +--------------+------------+----------------------+--------+--------------+-------------------+------------------+
And compare with table definition:
mysql> SHOW CREATE TABLE sakila.inventory \G
Table: inventory
Create Table: CREATE TABLE `inventory` (
`inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`film_id` smallint(5) unsigned NOT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`inventory_id`),
KEY `idx_fk_film_id` (`film_id`),
KEY `idx_store_id_film_id` (`store_id`,`film_id`),
CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8
In the above example, note the following:
- The PRIMARY key is on inventory_id column, hence contains one column exactly. Since it is UNIQUE, it is certain to have 1 row per key.
- The idx_fk_film_id index is non unique. The INFORMATION_SCHEMA.INNODB_INDEX_STATS table lists two values for that column, although it only indexes one column only. This is because it implicitly includes the PRIMARY key for that table (as with all InnoDB keys). However, in innodb_index_rows only the explicitly indexed columns are listed.
- The idx_fk_film_id index provides with 5 rows per film_id value (this is of course an average estimation).
- The idx_store_id_film_id key is a compound index over two columns. If we use this index on filtering by store_id only, we expect to get 4478 per store_id. If we also filter by film_id, we expect to get fewer results: we only expect 2 rows per store_id:film_id combination.