What is a covering index in MySQL?
Why Interviewers Ask This
This question targets practical, hands-on experience with MySQL / SQL. Interviewers want to see if you've worked with these concepts in real projects, not just read about them. Strong answers include concrete examples.
Answer
A covering index is an index that contains all the columns needed to satisfy a query — the query can be answered entirely from the index without accessing the actual table rows. This is highly efficient because it avoids the secondary lookup from the index to the table. EXPLAIN shows "Using index" in the Extra column when a covering index is used. Example: SELECT email, name FROM users WHERE status = "active"; — if you create INDEX idx_status_email_name (status, email, name), MySQL can get all three columns (status for filtering, email and name for returning) from the index alone, never touching the table. Rules for covering index design: the index must include: (1) columns in the WHERE clause (leftmost), (2) columns used in JOIN ON, (3) columns in SELECT. Covering indexes are most impactful for read-heavy queries on large tables. Trade-off: larger indexes consume more disk space and slow down writes. Always measure with EXPLAIN before and after. For InnoDB, remember that secondary indexes implicitly include the primary key — INDEX (email) actually stores (email, id), so queries that also need id are covered.
Common Mistake
Rushing to answer is a common mistake. Take two seconds to structure your response: definition → example → trade-off. This structure makes complex MySQL / SQL answers easy to follow.