Short answer
An index is a separate data structure — usually a B-tree — that keeps selected column values sorted with pointers to rows, so the database can find matches without scanning the whole table. Indexes speed up reads, filters, joins and sorting, but they use storage and slow down inserts and updates because every index must be maintained.
Choosing indexes
- Index columns used in WHERE, JOIN and ORDER BY on large tables.
- Composite indexes follow the left-most prefix rule: (a, b) helps queries on a, or a and b — not b alone.
- Prefer selective columns; indexing a boolean rarely helps.
- Covering indexes include every column a query needs, avoiding table lookups.
Verify with the query plan
Use EXPLAIN (or EXPLAIN ANALYZE) to confirm the index is used. Functions on indexed columns, leading wildcards in LIKE and implicit type casts can all prevent index use.
How to answer it in an interview
- Mention the write-amplification trade-off unprompted.
- Describe how you found and fixed a slow query with EXPLAIN.