Why Using PRIMARY Key Index in MySQL COUNT(*) Can Be Slower: Analysis and Findings

This article documents a performance investigation where counting rows in a MySQL table with SELECT COUNT(*) FROM test2 took 0.13 s, while forcing the use of the PRIMARY key index with SELECT COUNT(*) FROM test2 USE INDEX (PRIMARY) took 0.15 s, a 0.02 s slowdown.

Problem Description

The author repeatedly executed both queries three times each and observed consistent timing differences, prompting a deeper analysis of why the primary‑key index appears slower.

Execution Plan Comparison

root@localhost# mysql.sock : tc0112:33:34> explain select count(*) from test2;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+
| 1  | SIMPLE      | test2 | NULL       | index | NULL          | index_vote_num | 4       | NULL | 1114117 | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+

root@localhost# mysql.sock : tc0112:33:35> explain select count(*) from test2 use index (PRIMARY);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1  | SIMPLE      | test2 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1114117 | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

The plans differ only in the key column (index_vote_num vs. PRIMARY), yet the PRIMARY key path incurs extra time.

Index Information

root@localhost# mysql.sock : tc0112:34:27> show index from test2;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test2 | 0          | PRIMARY        | 1            | id          | A         | 1054391     | NULL     | NULL   |      | BTREE      |         |               |
| test2 | 1          | index_name     | 1            | name        | A         | 1055674     | NULL     | NULL   |      | BTREE      |         |               |
| test2 | 1          | index_vote_num | 1            | vote_num    | A         | 9929        | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The Cardinality column shows the estimated number of distinct values for each index.

Profiling the Queries

root@localhost# mysql.sock : tc0112:41:21> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 1120198 |
+----------+
1 row in set (0.14 sec)

root@localhost# mysql.sock : tc0112:41:33> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| Sending data         | 0.133695 | 0.133527 | 0.000000   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+

root@localhost# mysql.sock : tc0112:41:56> select count(*) from test2 use index (PRIMARY);
+----------+
| count(*) |
+----------+
| 1120198 |
+----------+
1 row in set (0.15 sec)

root@localhost# mysql.sock : tc0112:41:56> show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| Sending data         | 0.149678 | 0.149268 | 0.000191   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+

The Sending data phase consumes more time when the PRIMARY key is forced, accounting for the observed slowdown.

Optimizer Trace

set optimizer_trace='enabled=on';
set optimizer_trace_max_mem_size=1000000;
set end_markers_in_json=on;
select count(*) from test2;
SELECT * FROM information_schema.optimizer_trace\G
... (trace output omitted) ...

The trace did not reveal obvious differences between the two execution paths.

System Call Tracing (strace)

strace -o ./xxxx.log -T -tt -f -p `pidof mysqld`
... (selected excerpts) ...
2779  17:31:57.090832 recvfrom(19, "\3select count(*) from test2", 27, MSG_DONTWAIT, NULL, NULL) = 27 <0.000011>
2779  17:31:57.221047 sendto(19, "...count(*)...", 62, MSG_DONTWAIT, NULL, 0) = 62 <0.000157>
... (similar entries for the PRIMARY‑index query) ...

More system‑call steps are observed for the PRIMARY‑index query, which contributes to the extra latency.

InnoDB Buffer Pool Statistics

show status like 'innodb_buffer%';
... (output showing read_requests) ...
Innodb_buffer_pool_read_requests  = 121120574 (no index)
Innodb_buffer_pool_read_requests  = 121251637 (PRIMARY index)
Difference ≈ 131 063 vs. 125 971 logical reads (≈5 092 more reads with PRIMARY).

The logical‑read count is higher when using the PRIMARY key, confirming that the index choice affects I/O.

Conclusion

The execution plans differ only in the chosen index, yet the PRIMARY key path incurs extra CPU time in the Sending data stage.

Logical reads are higher when the PRIMARY key is forced, as shown by InnoDB buffer‑pool statistics.

The analysis demonstrates several MySQL diagnostic techniques (EXPLAIN, PROFILE, optimizer_trace, strace, buffer‑pool stats) that can be applied to similar performance puzzles.

Further investigation into MySQL’s cost‑based optimizer may reveal why the PRIMARY key index is not always the fastest choice for simple COUNT(*) queries.