Indexing & Query Optimization Interview Questions

Index Condition Pushdown (ICP) evaluates part of the WHERE on the storage-engine side using secondary-index columns, shown as Using index condition. Without ICP, InnoDB returns whole rows and the server filters later. ICP shines on range conditions on later columns of a composite key. It does not replace a covering index when you selected extra columns.

Range access on the first differing column still uses the composite key, but a gap (a > 5 AND b = 1) cannot use b as a tight equality in older optimizer versions. 8.0 range access plus ICP can filter b from the index. Putting the range column last (equality, then range, then ORDER BY) matches how B-trees work. EXPLAIN key_len shows how many prefix bytes were used.

Multi-valued indexes on JSON arrays let you look up elements without generating virtual columns by hand. EXPLAIN shows the functional index and type=ref for MEMBER OF / JSON_CONTAINS paths that match the key. Writes pay extra secondary inserts per array element. They are useless if the query wraps the JSON in a function the index definition does not match.

The optimizer may pick a worse index when cardinality estimates from sampled stats are off after a bulk load. Index dives on eq_range can also overestimate. Histogram + ANALYZE TABLE usually beats adding yet another overlapping key. PERFORMANCE_SCHEMA events_statements_history shows which index was used in production, not just EXPLAIN on a cold buffer pool.

Skip-scan and loose index scan (EXPLAIN Extra: Using index for group-by) can aggregate MIN/MAX or DISTINCT from a composite index without visiting every leaf. Tight index scan still walks the range. It requires the GROUP BY columns to be a leftmost prefix and no extra WHERE that breaks the skip. If Extra disappears after adding a filter, you fell back to a temp table.

ICP plus MRR (Multi-Range Read) reorders clustered lookups by PK to reduce random IO after a secondary range. Extra shows Using index condition; Using MRR. On SSD the MRR win is smaller but still helps huge ranges. Disabling mrr for tiny point lookups can be right—measure with EXPLAIN ANALYZE. Buffer-pool hit rate still dominates if the working set fits.

Functional indexes (8.0.13+) on expressions such as ((cast(json_col->>'$.id' as unsigned))) index the expression without a stored generated column. The SELECT/WHERE expression must match the index definition or the optimizer ignores it. Writes evaluate the expression into the secondary tree. Generated stored columns still win when you also need the value in SELECT *.

InnoDB's default index is a B+tree; the leaf of the PRIMARY KEY is the row itself (clustered index). A secondary index leaf stores the indexed columns plus the primary-key columns as a bookmark. A lookup that is not covering therefore does two trees: secondary then clustered. Choosing a wide VARCHAR PK makes every secondary index fatter.

A covering index stores every column the query needs in the secondary tree, so Extra shows Using index and InnoDB never visits the clustered page. SELECT * almost never covers. Adding one extra selected column can flip a cheap index-only scan into random PK lookups. Interviewers want you to name the columns, not just say add an index.

Leftmost prefix means (a,b,c) can serve WHERE a= and WHERE a= AND b=, but not WHERE b= alone. A range on a stops b and c from being used as equality. Skip-scan in 8.0 can sometimes probe later columns, but you should not design for it. Put equality filters first, then the ORDER BY column, then covering leftovers.

EXPLAIN type=ref with a tiny rows estimate is usually a good secondary lookup; type=ALL plus huge rows is a clustered scan. Extra: Using filesort or Using temporary often means a missing ORDER BY/GROUP BY index. filtered far below 100 with type=ALL means a WHERE applied after the scan. EXPLAIN ANALYZE (8.0.18+) compares estimates to actual loops.

Using filesort in Extra means InnoDB could not satisfy ORDER BY from an index and so sorts in a sort buffer or on disk. It is not always a disk file—small sorts stay in sort_buffer_size. An index on the ORDER BY columns, matching WHERE equality, removes it. LIMIT without a matching index still filesorts the whole filtered set.

Histogram statistics (ANALYZE TABLE UPDATE HISTOGRAM) help the optimizer when a column is not indexed but has skewed values—status enums, country codes. They do not replace an index for large filters. Persistent stats (mysql.innodb_index_stats) still sample the B-tree. Stale histograms after a bulk load are a common wrong-plan cause.

Invisible indexes stay maintained on writes but are ignored by the optimizer, so you can hide a candidate before DROP INDEX. If a forgotten query regresses, ALTER INDEX ... VISIBLE brings it back instantly. They still cost redo and change-buffer work, so they are an experiment, not a free extra index. Check SHOW INDEX Extra for the Invisible flag.

A redundant index is one whose leftmost columns are a prefix of another index; InnoDB maintains both on every INSERT. (a) is redundant if (a,b) exists, unless (a) is UNIQUE and (a,b) is not. Duplicate UNIQUE keys also double the clustered bookmark lookups. sys.schema_redundant_indexes lists them; dropping after an invisible test is the safe path.

FORCE INDEX should be a last resort after you confirm statistics are stale; it hides the real cardinality problem and breaks when data shape changes. ANALYZE TABLE or raising innodb_stats_persistent_sample_pages is the durable fix. Hints also fight index dives (eq_range_index_dive_limit). If two indexes look similar, histograms beat a hint.

Descending indexes (MySQL 8.0) store the B-tree in reverse so ORDER BY col DESC, other ASC mixed sorts can avoid filesort. A backward scan of an ascending index cannot mix directions in one tree. Replication of DESC keys requires 8.0 on every replica. Confirm Extra no longer says Using filesort after the ALTER.

Index merge (intersect/union) in Extra means two single-column indexes were combined instead of a composite key. Intersect ANDs rowids; union ORs them. It is often slower than one well-ordered (a,b) index because of extra clustered bookmarks. Seeing merge repeatedly is a schema signal, not a badge of optimizer cleverness.

Changing innodb_stats_persistent_sample_pages raises histogram and index-stat quality on huge tables at ANALYZE time. Too few pages make a unique-looking prefix look rare and the optimizer picks type=ALL. mysql.innodb_table_stats last_update tells you staleness. Recalculate after a bulk load before you add FORCE INDEX.

A hot row updated in place still dirties the clustered leaf; secondary indexes only change if their key columns or the PK bookmark change. Change buffer may delay those secondary writes. A covering index that includes a frequently updated column turns every SET into extra secondary I/O. That is why covering every SELECT * column is not free.