Indexes Interview Questions

An index is usually a B-tree that lets the database find rows without scanning the whole table. It helps WHERE, JOIN, and ORDER BY on high-cardinality columns. It can make things slower on writes, because every INSERT, UPDATE, and DELETE has to maintain the index. Too many indexes also waste memory. A low-cardinality column like a boolean often does not help. A wrong index can still be chosen and then need extra lookups back to the table. A covering index that includes the selected columns can avoid the table lookup. In an interview I would say I always look at EXPLAIN before I add another index.

A composite (user_id, created_at) in that order, because equality then range. Reversing it is worse for this predicate. I would not index created_at alone if every query always has user_id. I mention covering extra selected columns only if the table is wide and the query is hot. I would look at EXPLAIN instead of adding five indexes on day one.

Writes pay for every index. A bad index can also make the planner pick nested loops that look cheap and are not. Functions on the column, like WHERE YEAR(date) = 2024, can disable the index. Tiny tables can be slower with index lookup than a seq scan. I would say I measure. 'More indexes' is not a strategy. I also mention fragmentation and outdated stats.

An index that contains every column the query needs, so the engine does not visit the table heap. In Postgres that might be an INCLUDE column. In InnoDB secondary indexes already include the PK. I would cover a hot API that only needs id, status, created_at. I would not cover SELECT * . Covering is a read optimization I mention for one hot query, not for every table.

Slow query log, pg_stat_statements, or APM. I look at total time, not just one slow call. I EXPLAIN ANALYZE in staging with realistic data. I would not add an index from a guess on a 10-row laptop table. I mention that parameter sniffing and data skew can make a good-looking index still miss. Measure, then index, then measure again.