MySQL index size doesn’t include all values

indexinnodbMySQLperformance-tuningsize;

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.