Understanding Full Table Scan vs Full Primary Key Scan in MySQL InnoDB

The article investigates why MySQL's EXPLAIN may show type = ALL (full table scan) while the actual execution uses a full primary‑key scan, using MySQL 8.0.32 with the InnoDB storage engine as a case study.

1. Preparation

A test table t2 is created and populated:

CREATE TABLE `t2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t2` (`id`,`i1`) VALUES
(1,20),(2,21),(3,22),(4,23),(5,23),(6,33);

Two simple queries are used for comparison:

SELECT `i1` FROM `t2`;

SELECT `id` FROM `t2`;

2. Execution‑Plan Comparison

Running EXPLAIN on the first query yields type = ALL , indicating a full table scan. The second query shows type = index with key = PRIMARY , meaning a full primary‑key scan (covering index).

Both scans read data from the primary‑key index because InnoDB stores the entire row in the primary‑key B‑tree.

3. Execution‑Flow Comparison

Debugging the source code reveals that the decision point is inside JOIN::adjust_access_methods() . For the first query the condition if (tab->type() == JT_ALL) stays true, leading to a full table scan. For the second query the expression !tab->table()->covering_keys.is_clear_all() evaluates to true, allowing the optimizer to switch to JT_INDEX_SCAN , i.e., a full primary‑key scan.

Both paths eventually call ha_innobase::index_first() → ha_innobase::index_read() → row_search_mvcc() to fetch the first record, then ha_innobase::general_fetch() for subsequent rows. The underlying index opened in both cases is the primary‑key index.

When examining the retrieved fields, the full table scan reads column i1 , while the full primary‑key scan reads column id , confirming that the optimizer treats the primary key as a covering index when only the primary‑key column is needed.

4. Summary

From a runtime perspective, both full table scans and full primary‑key scans read all leaf pages of the primary‑key B‑tree, so their I/O cost is essentially identical for InnoDB tables. The distinction matters for other storage engines (e.g., MyISAM) where data and indexes are stored separately, and MySQL keeps the terminology uniform across engines.