Performance Comparison of MySQL COUNT Queries on InnoDB and MyISAM

1. Practice

I created a table named user containing one million rows with the following structure:

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;

The table has a primary‑key index on id . I then executed two different COUNT statements and measured their execution time; both took about 0.14 s (see the first screenshot). I also tried two other COUNT variants, which showed similar timings (second screenshot). The id column is the primary key, while username and address are ordinary columns. Using the primary key gives a slight advantage that becomes more noticeable with larger data sets.

2. EXPLAIN Analysis

Running EXPLAIN on the four queries reveals their execution plans (see the diagram). The first three queries share the same plan, while the last two share another plan.

type : The first three have index (full index scan), meaning only the index is read. The last two have all (full table scan), meaning no index is used.

key : Shows which index the optimizer chooses; PRIMARY indicates the primary‑key index, NULL means no index.

key_len : Length of the used key; for the integer primary key it is 4 bytes.

Extra : Using index means the optimizer can satisfy the query using only the index without a table‑row lookup.

Thus the first three COUNT methods are faster because they use an index, whereas the latter two are slower due to full‑table scans.

3. Principle Analysis

3.1 Primary‑Key Index vs. Secondary Index

In InnoDB, all indexes are stored as B+ trees. In a primary‑key (clustered) index, leaf nodes contain the full row data. In a secondary index, leaf nodes store only the primary‑key values; to retrieve a row the engine must first locate the primary key in the secondary leaf and then fetch the row from the clustered index (the so‑called “back‑to‑table” operation).

3.2 Detailed Principle

The COUNT function is an aggregate function, so MySQL must examine each row (or index entry) that contributes to the result.

SELECT COUNT(1) FROM user; – InnoDB walks the smallest index tree (not necessarily the primary key) and increments a counter for each leaf entry without reading the actual row data.

SELECT COUNT(id) FROM user; – InnoDB traverses the primary‑key index, reading the id values directly from the leaf nodes; because the primary key is stored in the leaf, no extra I/O is required.

SELECT COUNT(username) FROM user; – InnoDB performs a full table scan, reading each row’s username . If the column is defined NOT NULL , it simply counts rows; otherwise it must check for NULL values.

SELECT COUNT(*) FROM user; – MySQL optimises this special case: it uses the smallest index tree to count entries, similar to COUNT(1) , because the engine recognises that only the total row count is needed.

If a secondary index on username is added, EXPLAIN SELECT COUNT(*) FROM user; will show that the optimizer chooses the username index, confirming the earlier analysis.

Overall, the performance ranking is: COUNT(1) (fastest), COUNT(id) (second), COUNT(*) (similar to COUNT(1) when a suitable index exists), and COUNT(username) (slowest due to full‑table scan).

4. What About MyISAM?

In the MyISAM engine, SELECT COUNT(*) FROM user; is extremely fast because MyISAM stores the total row count on disk, allowing the server to read it directly without scanning any index or table.

MyISAM achieves this speed by not supporting transactions; therefore it does not need the MVCC machinery that InnoDB uses. InnoDB must check each row’s visibility according to the current transaction snapshot, which adds overhead.

MVCC (Multi‑Version Concurrency Control) is a broad topic; I will discuss it in more depth in a future article.

Feel free to leave comments or questions.