Understanding MySQL Covering Indexes, Table Lookups, and the Leftmost Prefix Rule

We recently reviewed MySQL knowledge and interviewed candidates about common interview questions, noting that interview topics have become increasingly complex and often focus on real project issues, especially database-related problems.

Covering Index

A covering index (or covering index) is an optimization technique where all columns needed by a query are stored in the index, allowing the database engine to retrieve data directly from the index without accessing the table, thus reducing I/O and improving query speed.

Reduce I/O: The query can fetch data directly from the index, avoiding a table lookup.

Increase query speed: Fewer I/O operations lead to faster execution.

Index selectivity: Higher selectivity (unique values vs. total rows) means the index filters more rows, improving efficiency.

Interviewers often follow up by asking about table lookups (back‑table operations).

SQL Back‑Table

In MySQL (especially InnoDB), a back‑table operation occurs when a non‑clustered (secondary) index is used. The secondary index stores the indexed column values and the primary key; to retrieve non‑indexed columns, the engine must use the primary key to fetch the full row from the clustered index, which is called “back‑table”.

Back‑Table Principle

Non‑clustered index structure: Leaf nodes store (indexed column value, primary key).

Query process: The secondary index finds matching primary keys, then the engine looks up the full rows in the clustered index.

Example:

Given a users table with id (PK), name , and age , and a secondary index on name :

Query SELECT * FROM users WHERE name='Tom' triggers a back‑table because the engine first finds the id via the secondary index, then retrieves the full row.

Query SELECT id, name FROM users WHERE name='Tom' does not trigger a back‑table since all required columns are present in the secondary index.

Back‑table operations increase I/O and can become performance bottlenecks on large tables; adding needed columns to the index (creating a composite or covering index) can eliminate the back‑table.

Leftmost Prefix Rule

When using composite (multi‑column) indexes, the leftmost prefix rule states that the query must reference the leftmost column(s) of the index to use it effectively.

For a composite index on ( last_name , first_name ), the following queries illustrate the rule:

Query by last_name : SELECT * FROM employees WHERE last_name = 'Smith'; – uses the index.

Query by last_name and first_name : SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John'; – uses the index.

Query by first_name only: SELECT * FROM employees WHERE first_name = 'John'; – does NOT use the index because the leftmost column last_name is missing.

When creating composite indexes, place the most frequently queried columns on the left. However, the rule is a guideline; actual index design should consider query patterns and data distribution.

Understanding these concepts helps you design efficient indexes and avoid performance pitfalls during interviews and real‑world development.