I have a table with ~1.4M rows and an id
field as primary key and not all primary keys are included in index:
mysql> SELECT DISTINCT(COUNT(id)) FROM my_table;
+-------------+
| (COUNT(id)) |
+-------------+
| 1391736 |
+-------------+
1 row in set (0.23 sec)
And the index doesn't contain all primary keys
mysql> SHOW INDEX FROM my_table;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| my_table | 0 | PRIMARY | 1 | id | A | 1217360 | NULL | NULL | | BTREE | | | YES | NULL |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
As you can see SELECT DISTINCT(COUNT(
yields 1391736 values, whereas the Index carnality is 1217360, so about 13% of ids are missing from index.
I had a look at getting innodb buffer usage and it seems InnoDB has free space still:
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_buffer_pool_pages_free | 697127 |
+-------------------------------+--------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';
+--------------------------------+--------+
| Variable_name | Value |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_total | 786432 |
+--------------------------------+--------+
So I'm confused as to why not all ids are in the index: Can you point me to some commands I could run to identify where bottleneck is so I can fine-tune configuration to ensure all ids are in the index please?
Best Answer
Thanks @Akina for pointing me in the right direction: https://stackoverflow.com/questions/16732980/why-cardinality-value-in-mysql-indexes-dont-equal-distinct-count-for-column-val
Running
ANALYZE TABLE my_table
updated the cardinality to match index size.