Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis

Introduction

Using the following SQL statements often leads to slow queries:

SELECT COUNT(*) FROM SomeTable
SELECT COUNT(1) FROM SomeTable

Although many claim that a full‑table scan is inevitable, MySQL actually optimizes COUNT(*) without a WHERE clause by choosing the cheapest auxiliary index, which can be the most performant option in versions 5.6 and later.

How MySQL Calculates Index Execution Cost

When multiple indexes exist, MySQL selects the one with the lowest estimated cost, which consists of two components:

IO cost – reading data pages from disk; each page read costs 1 unit.

CPU cost – evaluating rows and sorting; default cost per row is 0.2.

Example Setup

We create a table with a primary key and two secondary indexes ( name_score and create_time) and insert 100,000 rows via a stored procedure.

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;

CREATE PROCEDURE insert_person()
BEGIN
  DECLARE c_id integer 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

After populating the table, we run EXPLAIN SELECT COUNT(*) FROM person and observe that MySQL uses the create_time auxiliary index, confirming the optimizer’s cost‑based choice.

When MySQL Chooses a Full Table Scan

Running a query with both NAME and create_time conditions results in a full table scan, even though both secondary indexes could be used. Forcing the create_time index reduces execution time from 4 ms to 2 ms, showing that the optimizer’s cost estimate was inaccurate.

-- Full table scan time: 4.0 ms
SELECT create_time FROM person WHERE NAME > 'name84059' AND create_time > '2020-05-23 14:39:18';

-- Using covering index time: 2.0 ms
SELECT create_time FROM person FORCE INDEX(create_time) WHERE NAME > 'name84059' AND create_time > '2020-05-23 14:39:18';

The cost of a full scan is derived from the estimated row count (CPU cost) and the number of data pages (IO cost). Using SHOW TABLE STATUS LIKE 'person' we find 100,264 rows (CPU cost ≈ 20,052.8) and 353 pages (IO cost = 353), giving a total cost of 20,406.

Using optimizer_trace to Verify Costs

Enabling optimizer_trace shows detailed cost estimates for each access path. The name_score index has a cost of 30,447, the create_time index 60,159, while the full scan remains at 20,406, confirming why the optimizer selects the scan.

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

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

{"chosen": true, "cost": 20406}

Conclusion

The optimizer’s cost model may not always match actual execution time, especially when statistics are inaccurate or when the estimated cost of using an index exceeds that of a full scan. Using EXPLAIN and optimizer_trace helps identify such mismatches and guide manual index hints or statistics updates.