/
innodb_index_rows.html
127 lines (109 loc) · 7.83 KB
/
innodb_index_rows.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
<h3>NAME</h3>
innodb_index_rows: Number of row cardinality per keys per columns in InnoDB tables
<h3>TYPE</h3>
View
<h3>DESCRIPTION</h3>
<p><i>innodb_index_rows</i> extends the <a href="http://www.percona.com/docs/wiki/percona-server:features:innodb_stats#innodb_index_stats">INNODB_INDEX_STATS</a> patch
in Percona Server, and presents with information on InnoDB keys cardinality as per indexed column.
</p>
<p>
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.
</p>
<p>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.
</p>
<p><i>innodb_index_rows</i> extends that information and adds column information.
It lists column names and their sequence within the key, and provides with easier to read format.</p>
<p>This view depends upon the <a href="http://www.percona.com/docs/wiki/percona-server:features:innodb_stats#innodb_index_stats">INNODB_INDEX_STATS</a> patch
in Percona Server.
</p>
<p>
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.
</p>
<h3>STRUCTURE</h3>
<blockquote><pre>
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 | |
+-------------------------+---------------------+------+-----+---------+-------+
</pre></blockquote>
<h3>SYNOPSIS</h3>
<p>Columns of this view:</p>
<ul>
<li><strong>TABLE_SCHEMA</strong>: Table schema of examined index</li>
<li><strong>TABLE_NAME</strong>: Examined index' table</li>
<li><strong>INDEX_NAME</strong>: name of index examined</li>
<li><strong>SEQ_IN_INDEX</strong>: position of column within index (<strong>1</strong> based)</li>
<li><strong>COLUMN_NAME</strong>: name of column within index</li>
<li><strong>is_last_column_in_index</strong>: boolean, <strong>1</strong> if current column is last in index definition.
<br/>The last column in an index is of particular interest since the number of rows per last column signifies the index's maximum selectivity</li>
<li><strong>incremental_row_per_key</strong>: Cardinality (number of values per key) of index up to and including current column</li>
</ul>
<h3>EXAMPLES</h3>
<p>Examine index cardinality on a specific table (discussion follows):</p>
<blockquote><pre>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 |
+--------------+------------+----------------------+--------------+--------------+-------------------------+-------------------------+
</pre></blockquote>
<p>Compare with call to INFORMATION_SCHEMA.INNODB_INDEX_STATS:</p>
<blockquote><pre>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 |
+--------------+------------+----------------------+--------+--------------+-------------------+------------------+
</pre></blockquote>
<p>
<p>And compare with table definition:</p>
<blockquote><pre>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
</pre></blockquote>
<p>
In the above example, note the following:
<ul>
<li>The <i>PRIMARY</i> key is on <i>inventory_id</i> column, hence contains one column exactly. Since it is UNIQUE, it is certain to have <strong>1</strong> row per key.</li>
<li>The <i>idx_fk_film_id</i> 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 <i>innodb_index_rows</i> only the explicitly indexed columns are listed.</li>
<li>The <i>idx_fk_film_id</i> index provides with <strong>5</strong> rows per <strong>film_id</strong> value (this is of course an average estimation).</li>
<li>The <i>idx_store_id_film_id</i> key is a compound index over two columns. If we use this index on filtering by <strong>store_id</strong> only, we
expect to get <strong>4478</strong> per <strong>store_id</strong>. If we <i>also</i> filter by <strong>film_id</strong>, we expect to get fewer results:
we only expect <strong>2</strong> rows per <strong>store_id</strong>:<strong>film_id</strong> combination.</li>
</ul>
</p>
<h3>ENVIRONMENT</h3>
Percona Server >= 5.5.8-20.0 with <a href="http://www.percona.com/docs/wiki/percona-server:features:innodb_stats#innodb_index_stats">INNODB_INDEX_STATS patch</a>
<h3>SEE ALSO</h3>
<a href="innodb_index_stats.html">innodb_index_stats</a>
<h3>AUTHOR</h3>
Shlomi Noach