Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan

Preface

Will SELECT COUNT(*) cause a full‑table scan and slow query? Many claim that without a WHERE clause MySQL can optimize COUNT(*) by using the cheapest secondary index, but this only applies to MySQL 5.6+.

To verify, I ran EXPLAIN SELECT COUNT(*) FROM SomeTable on a ten‑million‑row table and observed that MySQL chose a secondary index, confirming the claim.

How MySQL Calculates Index Selection Cost

When multiple indexes exist, MySQL estimates the execution cost based on two components:

IO cost – reading data pages from disk. Each page read costs 1 unit; the cost depends on page size.

CPU cost – evaluating rows and sorting, defaulting to 0.2 per row.

Example

First, create a test table (MySQL 5.7.18):

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;

Insert 100,000 rows with a stored procedure:

CREATE PROCEDURE insert_person()
BEGIN
  DECLARE c_id INT DEFAULT 1;
  WHILE c_id <= 100000 DO
    INSERT INTO person VALUES (c_id, CONCAT('name',c_id), c_id+100,
      DATE_SUB(NOW(), INTERVAL c_id SECOND));
    SET c_id = c_id + 1;
  END WHILE;
END;

Run EXPLAIN SELECT COUNT(*) FROM person and MySQL picks the create_time secondary index, confirming that the optimizer selects the lowest‑cost path.

Next, test a query with two conditions:

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

The optimizer chooses a full table scan, even though a covering index ( name_score or create_time) could be used. Forcing the create_time index shows the query runs in about 2 ms versus 4 ms for the full scan, indicating the cost estimate was inaccurate.

Cost breakdown for the full scan:

Rows ≈ 100,264 → CPU cost = 100,264 × 0.2 = 20,052.8

Data length ≈ 5,783,552 bytes → 353 pages (16 KB each) → IO cost = 353

Total cost = 20,052.8 + 353 ≈ 20,406.

Using optimizer_trace (MySQL 5.6+), we can see the estimated costs for each access path:

{"index":"name_score","rows":25372,"cost":30447}

{"index":"create_time","rows":50132,"cost":60159}

{"access_type":"scan","rows_to_scan":100264,"cost":20406,"chosen":true}

The full‑scan cost (20,406) is the smallest, so the optimizer selects it despite the actual execution time being higher.

Conclusion

The optimizer’s cost model may not always reflect real‑world performance, especially when statistics are stale or when covering indexes are involved. In production, use EXPLAIN and optimizer_trace to verify the chosen plan and adjust indexes or query hints accordingly.

Thank you for reading.