1

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?

  • 1
    SELECT COUNT(id) FROM my_table; gives accurate value. SHOW INDEX FROM my_table; gives approximate value. – Akina Jun 18 at 12:45
  • You (the OP) probably meant select count(distinct id).... Also distinct is not a function. – mustaccio Jun 18 at 13:00
  • @mustaccio That's true - but in this particular case id is PK, so DISTINCT makes no sense at all. – Akina Jun 18 at 13:03
0

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.

| improve this answer | |
0

The numbers in EXPLAIN and SHOW INDEX and SHOW TABLE STATUS are approximate. Live with it. Even ANALYZE, in some situations, will be approximate.

COUNT(x) counts how many rows have x IS NOT NULL. Usually you want to say COUNT(*) to count all rows (after filtering by WHERE. If id is "unique", then there will be no difference between COUNT(id) and COUNT(DISTINCT id). If id is the PK, then it is also NOT NULL, so those will also be the same as COUNT(*).

Innodb_buffer_pool... status values will fluctuate. And there will appear to be some "free" space that never gets used. Live with it. It's an optimization. Don't you buy a new tube of toothpaste before you run out?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.