SQL Indexes Interview Questions and Answers

Indexing is the SQL topic that decides whether a query returns in milliseconds or minutes, so it appears in almost every backend, data-engineering and analyst interview above the junior level. Interviewers use it to test whether you understand how data is stored and searched, not just whether you can type CREATE INDEX.

The examples below use orders(id, customer_id, status, created_at, total) and standard SQL. Behaviour is described for B-tree indexes, which are the default in PostgreSQL, MySQL/InnoDB, SQL Server and Oracle.

Why do interviewers ask about indexes?

Because indexing is the clearest test of the read/write trade-off at the heart of database design. Every index makes some reads faster and every write slower, and choosing the right set proves you think about workloads rather than memorizing commands.

An index is a sorted, separately-maintained data structure — almost always a B-tree — that lets the engine find rows without scanning the whole table. The cost is storage plus maintenance on every modification. Answer index questions by naming both sides of that trade every time.

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

A B-tree keeps keys sorted in a shallow, balanced tree. Instead of scanning N rows (O(N)), a lookup walks from the root to a leaf in O(log N) steps, and range scans read a contiguous run of leaves.

That structure is why a B-tree serves equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY) and prefix LIKE 'abc%' — all of them exploit the sorted order. A hash index, by contrast, serves only equality.

CREATE INDEX idx_orders_created ON orders (created_at);
-- Uses the index: range scan over sorted leaves
SELECT * FROM orders WHERE created_at >= '2026-01-01';

The depth stays tiny even for huge tables — a few levels cover millions of rows — which is the intuition to state: "log-base-high-fanout of N is a very small number."

Interview note: Follow-up: "when is a hash index better than a B-tree?" Only for pure equality lookups with no range or ordering needs — rare enough that B-tree is the default everywhere.

Q2. Clustered vs non-clustered index — explain the difference.

A clustered index stores the table rows in the index's key order, so its leaf level is the data and there can be only one per table. A non-clustered index is a separate structure of key plus a pointer back to the row, and a table can have many.

In SQL Server and MySQL/InnoDB the primary key is the clustered index by default, so the physical row order follows the PK. A non-clustered index lookup finds the key, then follows the pointer to fetch the rest of the row — the extra step called a "bookmark lookup" or "key lookup".

-- InnoDB: PK is clustered; this secondary index is non-clustered
CREATE INDEX idx_orders_customer ON orders (customer_id);

Note the dialect nuance: PostgreSQL heap tables have no clustered index in the SQL Server sense (the CLUSTER command is a one-time physical reorder, not maintained). Mention this and you show you know indexing is engine-specific.

Interview note: Trap: "why can there be only one clustered index?" Because the rows can be physically sorted exactly one way; ordering them by two keys at once is impossible.

Q3. In a composite index on (a, b, c), which queries can use it?

Queries that filter on a leftmost prefix: a; a, b; or a, b, c. A query filtering only on b, only on c, or on b, c cannot use the index efficiently — this is the leftmost-prefix rule.

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

SELECT * FROM orders WHERE customer_id = 42;                          -- uses index
SELECT * FROM orders WHERE customer_id = 42 AND status = 'PAID';      -- uses index
SELECT * FROM orders WHERE status = 'PAID';                           -- cannot use it well

Column order is therefore a design decision driven by your query patterns: put the column used in every query first, and the column used for ranges last (a range stops the index from filtering later columns). This ordering rule is the most practical index insight you can demonstrate.

Interview note: Follow-up: "where do you place a column used only for ORDER BY?" After the equality-filter columns, matching the sort direction, so the index also satisfies the sort and avoids a separate sort step.

Q4. What is a covering index and why is it fast?

A covering index includes every column the query touches — filters and outputs — so the engine answers entirely from the index and skips the lookup back to the table row.

-- Query needs customer_id (filter) and total, status (output)
CREATE INDEX idx_cover ON orders (customer_id) INCLUDE (total, status);  -- SQL Server / PG

SELECT total, status FROM orders WHERE customer_id = 42;

The INCLUDE clause (PostgreSQL and SQL Server) stores extra columns at the leaf without making them part of the sort key. In MySQL you achieve the same by adding the columns to the composite key. Explaining that the win comes from eliminating the key lookup — not from the search itself — is the senior-level framing.

Interview note: Trap: "is a bigger covering index always better?" No — extra columns bloat the index, slowing writes and cache efficiency. Cover only the hot queries that justify it.

Q5. What is selectivity, and why does it decide whether an index is used?

Selectivity is the fraction of distinct values — high selectivity (many distinct values, like an email) makes an index useful; low selectivity (few values, like a boolean or a status with two options) often makes a full scan cheaper.

If a status column is 90% 'PAID', an index on it barely narrows the search, so the optimizer may scan the table instead — reading a filtered index and then fetching most rows individually is slower than one sequential scan.

-- Low selectivity: optimizer may ignore this on a mostly-PAID table
SELECT * FROM orders WHERE status = 'PAID';

The takeaway to state: index the columns your queries filter on and that are selective. A partial index (WHERE status = 'CANCELLED') can index just the rare, useful subset.

Interview note: Follow-up: "how does the optimizer know selectivity?" From table statistics/histograms. Stale statistics cause bad plans — running ANALYZE/UPDATE STATISTICS is the fix.

Q6. Why would a perfectly good index be ignored?

Because the predicate is not "sargable" — search-argument-able. Wrapping the column in a function, a leading wildcard, or an implicit type cast all prevent index use.

-- NOT sargable: function on the column defeats the index
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Sargable rewrite: index on created_at can be used
SELECT * FROM orders WHERE created_at >= '2026-01-01'
                       AND created_at <  '2027-01-01';

Other classic causes: LIKE '%abc' (leading wildcard can't use the sorted prefix), comparing an indexed VARCHAR column to a number (implicit cast), and OR across different columns. Being able to rewrite a non-sargable predicate into a sargable one is a frequently tested skill.

Interview note: Trap: "how do you confirm the index was skipped?" Read the execution plan (EXPLAIN / EXPLAIN ANALYZE). Never guess — the plan is the source of truth.

Q7. What is the cost of over-indexing?

Every INSERT, UPDATE and DELETE must maintain each affected index, so a table with ten indexes pays ten times the write overhead. Indexes also consume storage and compete for buffer-pool memory.

On a write-heavy table — an events or logging table — a lean set of indexes can outperform a heavily indexed one overall, because inserts stop stalling on index maintenance. The interview answer is the balance: enough indexes to serve real read queries, no more.

-- Find unused indexes before adding more (PostgreSQL)
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

Auditing for unused indexes and dropping them is a maintenance habit worth mentioning — it shows you manage the index set over time, not just at creation.

Interview note: Follow-up: "does an index slow down a bulk load?" Yes — a common pattern is to drop non-essential indexes, load, then rebuild them, which is faster than maintaining them row by row.

Q8. How do you decide which index to create for a slow query?

Read the execution plan to find the expensive step, identify the filter and join columns, and create an index whose leading columns match the equality filters, with range/sort columns last — then verify the plan changed.

EXPLAIN ANALYZE
SELECT total FROM orders
WHERE customer_id = 42 AND status = 'PAID'
ORDER BY created_at DESC;
-- Candidate: (customer_id, status, created_at) — equality first, sort column last

Naming the process — measure, design, verify — matters more than the specific index. Interviewers score whether you would confirm the improvement with the plan rather than assuming the index helped.

Interview note: Trap: "you added the index and it's still slow — now what?" Check statistics freshness, whether the predicate is sargable, and whether selectivity is too low for the index to help at all.

How to prepare

Load a table with a few hundred thousand rows and run EXPLAIN ANALYZE before and after creating each index — watching a sequential scan turn into an index scan makes the leftmost-prefix and sargability rules permanent. Deliberately break an index with YEAR(created_at) and then fix it with a range predicate; that single exercise inoculates you against the most common index interview trap.

Pair this with the normalization questions, since keys and normal forms drive which columns you index, and the constraints set, because primary and unique constraints create indexes automatically. For the storage model underneath, the SQL learning path is the foundation. A mock interview that hands you a slow query and a plan is the closest simulation of the real index round.