Indexing and Query Optimisation

Ask any senior engineer what fixed their last performance incident and the answer is often “an index”. Interviewers want to know whether you understand how an index works, when it is used, and how to prove it with an execution plan.

1. How does a B-tree index speed up a query?

A B-tree keeps keys sorted in a shallow, wide tree. Finding a key walks from root to leaf — three or four page reads even for millions of rows — instead of scanning the whole table. Because keys are sorted, it also helps range queries and ORDER BY.

2. Clustered vs non-clustered index?

A clustered index stores the table rows themselves in index order, so there can be only one (in InnoDB it is the primary key). A non-clustered (secondary) index stores the key plus a pointer — in InnoDB, the primary key — so a lookup may need a second step to fetch the row.

3. What is a composite index and the leftmost-prefix rule?

An index on several columns, sorted by the first column, then the second, and so on. It can serve queries that filter on a leftmost prefix of those columns. Put equality columns first and the range column last.

CREATE INDEX idx_orders_cust_status_date ON orders (customer_id, status, created_at);

-- uses the index
SELECT * FROM orders WHERE customer_id = 7 AND status = 'PAID' AND created_at >= '2026-01-01';
-- cannot use it efficiently (skips the first column)
SELECT * FROM orders WHERE status = 'PAID';

4. What is a covering index?

An index that contains every column the query needs, so the database answers from the index alone without touching the table. EXPLAIN shows “Using index” in MySQL or “Index Only Scan” in PostgreSQL.

CREATE INDEX idx_emp_dept_salary ON employees (dept_id, salary);
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;   -- index-only

5. Why might the database ignore your index?

• A function or calculation on the column: WHERE YEAR(created_at) = 2026 — rewrite as a range.
• A leading wildcard: LIKE '%phone'.
• Implicit type conversion (comparing a string column to a number).
• Low selectivity — when most rows match, a full scan is cheaper.
• Stale statistics.

-- index on created_at ignored
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- index used
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

6. How do you read an EXPLAIN plan?

Look at the access type for each table (full scan vs index lookup), which index was chosen, the estimated rows examined, and extra steps such as “Using filesort” or “Using temporary”. In PostgreSQL, EXPLAIN ANALYZE runs the query and shows real timings, so you can compare estimates with reality.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PAID' AND o.created_at >= now() - interval '7 days';

7. What is the cost of adding indexes?

Every insert, update and delete must also update each index, and indexes use disk and memory. Too many indexes slow down writes and can confuse the planner. Index for the queries you actually run, and drop unused ones.

8. A query that used to take 50 ms now takes 5 seconds. How do you investigate?

1) Reproduce and capture the exact query and parameters. 2) Run EXPLAIN and compare with the old plan. 3) Check data growth, missing or unused indexes, and stale statistics. 4) Look for lock waits. 5) Fix (index, rewrite, pagination with keyset instead of large OFFSET), then measure again.