Understanding MySQL Index Usage and Covering Indexes: Why Different Queries Yield Different Results

Background : While developing a cross‑city transaction visualization project, the author discovered inconsistent results when querying the apps table using the framework versus a hand‑written SQL statement.

Query Process :

SQL1 (framework query):

select * from apps limit 1;

SQL2 (hand‑written query):

select id from apps limit 1;

Both queries were labeled SQL1 and SQL2 for later discussion.

Investigation :

The table contains two rows with different id , city_code , company_code , and company_name values, and two indexes: a primary key on id and a unique index uniq_company_code on company_code .

Running EXPLAIN on the two queries shows that SQL1 does not use the uniq_company_code index because SELECT * requires columns not covered by that index, while SQL2 uses the index because it only selects id , which is stored in the index leaf.

Key Findings :

The optimizer prefers the shortest covering secondary index; it will not consider the full row length of the clustered primary key when a suitable secondary index exists.

If the selected columns are all present in a secondary index (covering index), MySQL can retrieve rows directly from the index without a table lookup, improving I/O efficiency.

Primary key indexes can also act as covering indexes, but secondary indexes are chosen first because they are smaller and consume less I/O.

Experiments removing the uniq_company_code index confirmed that the optimizer falls back to a full table scan for SQL2, demonstrating the importance of covering indexes.

Conclusion :

When no WHERE clause is present, MySQL may perform a full table scan; however, if an index can satisfy the query, the index order determines the result order.

Secondary indexes that include the primary key columns can serve as covering indexes, leading the optimizer to prefer them over the primary key.

Understanding index coverage and the optimizer's selection rules helps avoid unexpected query results and improves performance.

Author: Zhang Huan (PHP Senior Engineer, Lianjia Transaction R&D); Reviewers: Cai Baiyin, Zhong Yan.