SQL Interview Question: What Is an Index and When Should You Not Use One?

Short answer: an index is a separate, sorted data structure (usually a B-tree) that maps column values to row locations, so the database can find matching rows without scanning the whole table, much like a book's index.

CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT * FROM orders WHERE customer_id = 42;   -- index lookup instead of a full scan

What indexes help with

• Equality and range filters: =, <, BETWEEN, IN.
• ORDER BY on indexed columns (no separate sort).
• Joins on foreign keys.
• Uniqueness: a unique index enforces it.

Composite indexes

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);
-- helps:  WHERE customer_id = 42
-- helps:  WHERE customer_id = 42 AND created_at > '2026-01-01'
-- doesn't (efficiently): WHERE created_at > '2026-01-01'  (leftmost column missing)

When indexes hurt or don't help

• Write-heavy tables: every insert, update and delete must update each index.
• Low-selectivity columns (a boolean, a status with 3 values) where most rows match; a full scan may be faster.
• Small tables: scanning a few hundred rows is already fast.
• Functions on the column: WHERE LOWER(email) = ... can't use a plain index on email (use an expression index).
• Leading wildcards: LIKE '%son'.
• Storage: indexes take disk and memory.

Bonus points

• A covering index contains all columns a query needs, so the table isn't read at all.
• Use EXPLAIN to confirm an index is actually used.
• Clustered (InnoDB primary key) vs non-clustered indexes: in a clustered index the table rows themselves are stored in index order.