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

We recently studied MySQL and interviewed candidates about interview questions, noticing that interview topics have become increasingly complex and often focus on real project issues, especially those related to databases.

Covering Index

A covering index (also called a covering index) is an optimization technique where all columns required by a query are stored in the index, allowing the database engine to retrieve the data directly from the index without accessing the table rows, thus reducing I/O and speeding up queries.

Advantages include:

Reduced I/O: the index provides the data directly, eliminating the need for a table lookup.

Improved query speed: fewer I/O operations lead to faster execution.

Higher selectivity: a more selective index filters out more rows, further improving efficiency.

SQL Back Table

In MySQL InnoDB, a back‑table operation occurs when a secondary (non‑clustered) index is used to locate primary keys, and the engine must then fetch the full row from the clustered index (the primary key) to obtain columns not covered by the secondary index.

Back‑Table Principle

Secondary index stores (indexed column value, primary key).

Query process: first use the secondary index to find matching primary keys, then retrieve the full rows from the clustered index.

Example:

When executing SELECT * FROM users WHERE name='Tom', MySQL uses the secondary index on name to find the matching id, then looks up the full row by id, causing a back‑table lookup. The query SELECT id, name FROM users WHERE name='Tom' does not require a back‑table because all requested columns are present in the secondary index.

Back‑table lookups increase I/O and can become a performance bottleneck on large tables. Adding the needed columns to the index (creating a composite or covering index) can eliminate the back‑table step.

Leftmost Prefix Rule

The leftmost prefix rule applies to composite indexes. The query must reference the leftmost column(s) of the index to use it effectively.

For a composite index on (last_name, first_name):

Query SELECT * FROM employees WHERE last_name='Smith' can use the index.

Query

SELECT * FROM employees WHERE last_name='Smith' AND first_name='John'

can also use the index.

Query SELECT * FROM employees WHERE first_name='John' cannot use the index because it does not include the leftmost column.

When designing composite indexes, place the most frequently queried columns on the left side. However, the rule is a guideline; actual index usage depends on query patterns and data distribution.

Understanding covering indexes, back‑table operations, and the leftmost prefix rule helps you build efficient MySQL indexes and avoid common performance pitfalls.