How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans

MySQL chooses the index with the lowest estimated execution cost, which is composed of I/O cost (pages read from disk) and CPU cost (rows processed). By default, reading a data page costs 1, and evaluating a row costs 0.2.

When a query such as SELECT COUNT(*) FROM SomeTable is issued, MySQL may use a secondary index if it yields the smallest combined cost, even though the query does not reference any WHERE clause.

SELECT COUNT(*) FROM SomeTable

In a real‑world test on a 10‑million‑row table, EXPLAIN SELECT COUNT(*) FROM SomeTable showed that MySQL selected a secondary index ( create_time ) because the optimizer estimated its total cost to be lower than a full‑table scan.

The cost model can be illustrated with a simple table:

IO cost: each page read costs 1 (page size typically 16 KB for InnoDB).

CPU cost: each row examined costs 0.2.

For the example table person (created with two secondary indexes name_score and create_time ) we inserted 100 000 rows via a stored procedure and ran several queries.

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 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;

Running EXPLAIN SELECT COUNT(*) FROM person revealed that MySQL chose the create_time index, estimating a total cost of 20 406 (CPU = 100 264 × 0.2 = 20 052.8, IO = 353 pages × 1 = 353).

When we executed a range query with both NAME and create_time conditions, MySQL performed a full‑table scan despite the presence of covering indexes. Forcing the create_time index reduced execution time from 4 ms to 2 ms, showing that the optimizer’s cost estimate (601 59 for the index vs. 20 406 for the scan) was inaccurate.

Using optimizer_trace we can see the detailed cost breakdown for each candidate plan, confirming why the optimizer prefers one plan over another.

SET optimizer_trace="enabled=on";
SELECT create_time FROM person WHERE NAME > 'name84059' AND create_time > '2020-05-23 14:39:18';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

The analysis demonstrates that MySQL’s cost‑based optimizer may select a plan that is not the fastest in practice, especially when statistics are stale or when the cost model does not reflect actual I/O patterns. Therefore, developers should verify execution plans with EXPLAIN and optimizer_trace , and consider hints such as FORCE INDEX when necessary.

In summary, understanding how MySQL calculates index costs helps explain why certain queries use unexpected indexes, and provides practical guidance for tuning query performance in production environments.