Does SELECT COUNT(*) Trigger a Full Table Scan? MySQL Index Cost Deep Dive

The author asks whether SELECT COUNT(*) inevitably leads to a full‑table scan and notes a common claim that MySQL can use a secondary index for counting when there is no WHERE clause.

MySQL selects the index with the lowest estimated cost. The cost consists of two parts: IO cost (reading pages from disk, default 1 per page) and CPU cost (checking rows, default 0.2 per row).

To illustrate, a table person is created (MySQL 5.7.18) with a primary key id and two secondary indexes name_score (on name (191), score) and create_time. A stored procedure inserts 100 000 rows.

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name_score` (`name`(191),`score`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Running EXPLAIN SELECT COUNT(*) FROM person shows that MySQL uses the create_time secondary index, confirming that the optimizer picks the cheapest index for counting. Both COUNT(1) and COUNT(*) behave identically, and this optimization is available only in MySQL 5.6 and later.

A second query

SELECT * FROM person WHERE NAME > 'name84059' AND create_time > '2020-05-23 14:39:18'

results in a full‑table scan, even though the predicates could use either name_score or create_time. Forcing the create_time index with FORCE INDEX(create_time) yields a covering‑index scan that runs in about 2 ms, half the 4 ms of the full scan. The optimizer’s estimated costs are: name_score index: cost 30447 create_time index: cost 60159

full‑table scan: cost 20406

Because the full‑table‑scan cost is lowest, MySQL chooses it, even though the actual execution time is longer.

The full‑scan cost is calculated manually: rows ≈ 100264 (from SHOW TABLE STATUS), CPU cost = 100264 × 0.2 = 20052.8, pages ≈ 5783552 / 16 KB = 353, IO cost = 353 × 1 = 353, total cost = 20052.8 + 353 = 20406, matching the optimizer’s estimate.

Using optimizer_trace (enabled with SET optimizer_trace="enabled=on") reveals the same cost breakdown for each access path, confirming the manual calculation.

The analysis demonstrates that MySQL’s cost‑based plan selection does not always produce the fastest execution. In production, when multiple indexes exist, the optimizer may not pick the expected one. The author recommends inspecting queries with EXPLAIN and optimizer_trace to understand and tune the optimizer’s decisions.