Master MySQL Interview Questions: Covering Indexes, Back‑Table Lookups, and the Leftmost Prefix Rule

Covering Index

A covering index (also called a covering index) allows MySQL to satisfy a query using only the index data, without accessing the base table. When the required columns are all present in the index, the engine can retrieve rows directly from the index, dramatically reducing I/O and speeding up the query.

Reduce I/O: No table lookup is needed, so fewer disk reads occur.

Increase query speed: Fewer I/O operations translate into faster response times.

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

Interviewers often follow up by asking whether you understand the related concept of back‑table lookups.

SQL Back‑Table

In InnoDB, a back‑table (or “row lookup”) occurs when a query uses a non‑clustered (secondary) index that does not contain all the needed columns. The secondary index stores the indexed column values plus the primary key. To retrieve other columns, MySQL must use the primary key to fetch the full row from the clustered index, which is the “back‑table” step.

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

Query process: The engine first finds matching primary‑key values via the secondary index, then looks up those keys in the clustered index to obtain the complete row.

Example: given a users table (id, name, age) with a secondary index on name, the query SELECT * FROM users WHERE name='Tom' triggers a back‑table because MySQL must first locate the id via the secondary index and then fetch the full row from the primary index. In contrast, SELECT id, name FROM users WHERE name='Tom' can be satisfied entirely by the secondary index, avoiding a back‑table.

Back‑table operations increase I/O and can become a performance bottleneck on large tables or complex queries. A common mitigation is to create a composite (covering) index that includes all columns needed by the query, eliminating the need for a back‑table lookup.

Leftmost Prefix Rule

The leftmost prefix rule governs how MySQL uses composite (multi‑column) indexes. For the index to be applicable, the query’s WHERE clause must reference the leftmost column(s) of the index in order.

Query based on last_name only: SELECT * FROM employees WHERE last_name='Smith'; – uses the composite index.

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

Query based on first_name only: SELECT * FROM employees WHERE first_name='John'; – cannot use the composite index because it does not start with the leftmost column.

When designing composite indexes, place the most frequently filtered columns on the left. For example, if queries often filter by last_name and rarely by first_name, the index should be defined as (last_name, first_name), not the reverse.

While the leftmost prefix rule is essential, it is not an absolute mandate; real‑world decisions should consider query patterns, data distribution, and overall workload.