Why count(1) Isn’t Faster Than count(*) in MySQL – Real‑World Test

Someone claimed that count(1) is faster than count(*) in MySQL. This article disproves that claim with a detailed InnoDB test.

Conclusion: The performance difference between the two functions is negligible.

1. Practice

A table named user with one million rows was created. The schema includes a primary key id and three VARCHAR columns.

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Four count queries were executed. The first two ( count(1) and count(*)) took about 0.14 s each.

Counting by the primary key id showed a slight advantage.

2. EXPLAIN analysis

EXPLAIN reveals that the first three queries use type=index (index scan), while the last two use type=all (full table scan).

type : index means scanning only the index; all means a full table scan.

key : indicates which index is chosen (PRIMARY or NULL).

key_len : length of the used key (4 bytes for the INT primary key).

Extra : “Using index” shows that the optimizer can satisfy the query without reading the table rows.

3. Principle analysis

3.1 Primary vs. secondary index

In InnoDB, the leaf nodes of a primary (clustered) index store the full row data, while secondary indexes store only the primary key values, requiring an additional lookup (back‑table) to fetch the row.

3.2 How count works

select count(1) from user

makes InnoDB traverse the smallest index tree without reading row data, adding 1 for each leaf. select count(id) from user scans the primary index and reads the id values directly, which incurs no random I/O. select count(username) from user forces a full table scan because username is not indexed; MySQL must read each row and check for NULL. select count(*) from user is specially optimized: MySQL recognizes it as a total row count and uses the smallest index tree, effectively the same as count(1).

If a secondary index on username is added, EXPLAIN select count(*) from user shows that index being used, confirming the optimizer’s behavior.

4. MyISAM note

MyISAM stores the row count on disk, so count(*) is extremely fast. This is possible because MyISAM does not support transactions. InnoDB, which uses MVCC for transaction safety, must evaluate each row’s visibility, preventing such a shortcut.