HuntExams Academy logo
SQLIntermediate#indexes#performance

What is an index? What are the trade-offs?

Answer

A SQL index is a separate, sorted data structure (almost always a B-tree) that stores the indexed column values plus a pointer back to the row, so the database can jump straight to matching rows instead of scanning the whole table. Without an index the planner does a full table scan \u2014 O(n) reads; with a suitable index it does an index seek \u2014 roughly O(log n). What indexes speed up: - WHERE equality and range filters (WHERE email = ?, WHERE created_at > ?) - JOIN keys \u2014 foreign key columns are rarely indexed automatically - ORDER BY and GROUP BY, because the index is already sorted - Covering queries: if every column the query needs sits in the index, the engine never touches the table (index-only scan) - Uniqueness \u2014 a UNIQUE index is how the constraint is actually enforced The trade-offs interviewers want you to name: 1. Write cost \u2014 every INSERT, UPDATE and DELETE must maintain each index, so write throughput falls as index count grows. 2. Storage and memory \u2014 indexes often add 20-50% to table size and compete for the buffer pool / RAM cache. 3. Low selectivity is useless \u2014 an index on a two-value column such as is_active rarely helps; the optimiser prefers a scan when a query returns a large fraction of rows. 4. Column order matters in composite indexes \u2014 an index on (city, age) supports WHERE city = ? and WHERE city = ? AND age = ?, but not WHERE age = ? alone. That is the leftmost-prefix rule. 5. Functions break index usage \u2014 WHERE YEAR(created_at) = 2026 is non-sargable. Rewrite it as a range, or create a functional / computed index. 6. Fragmentation and stale statistics degrade query plans over time, so reindex and refresh statistics. Rule of thumb: index for your real read patterns, confirm with EXPLAIN, and drop indexes the planner never chooses.

Example

-- Slow: full table scan over 5M rows
SELECT id, name FROM users WHERE email = 'a@b.com';

CREATE INDEX idx_users_email ON users(email);
-- Now an index seek: a few page reads instead of thousands

-- Composite index: filter column first, then the sort column
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date DESC);
SELECT * FROM orders WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 20;

-- Covering index -> index-only scan
CREATE INDEX idx_orders_cover ON orders(customer_id, order_date, total);

-- Non-sargable vs sargable
SELECT * FROM orders WHERE YEAR(order_date) = 2026;          -- index NOT used
SELECT * FROM orders WHERE order_date >= '2026-01-01'
                       AND order_date <  '2027-01-01';       -- index used

-- Always verify the plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

Detailed explanation

An index is an auxiliary access structure that helps a database locate rows without examining the entire table. Most general-purpose relational databases commonly use B-tree-family indexes, which maintain ordered keys and row locators. However, not every index is sorted or separate from table data: hash indexes serve equality lookups, while clustered storage can hold the actual rows at the tree’s leaf level. Indexes matter because they can reduce the pages read for frequent queries, particularly on large tables.

A B-tree lookup follows a balanced path from the root through internal pages to a leaf. Finding a key takes roughly O(log n) traversal work, but returning results also requires reading matching index entries and possibly fetching table rows. A scan examines roughly O(n) rows, yet can outperform an index when many rows qualify. The optimiser estimates these costs using statistics, selectivity, table size and available access paths; creating an index does not guarantee its use.

Indexes can accelerate WHERE filters, joins and, when their ordering matches the query, ORDER BY or GROUP BY processing. For example, an index on orders(customer_id, order_date) can locate one customer’s orders and read them in date order, often avoiding a sort. Composite B-tree indexes generally work best when predicates constrain their leading columns; filtering only order_date may require a scan or an engine-specific optimisation. A covering index contains every column needed by a query, potentially avoiding table lookups, though visibility checks can still require them in PostgreSQL. Unique indexes commonly enforce uniqueness, while foreign-key columns are not automatically indexed in every database.

The trade-offs are additional storage, cache pressure and write-maintenance work. Inserts and deletes maintain relevant indexes; updates can require index changes when indexed values or row locations change. Low-selectivity filters may offer little benefit, although rare values or covering queries can still benefit. Functions on indexed columns may prevent efficient lookup unless an expression index or optimiser rewrite applies; a half-open date range is often preferable. Interviewers expect workload-based decisions: inspect EXPLAIN plans, measure actual performance and refresh statistics when needed. Rebuild indexes only for a demonstrated engine-specific need, and check constraints and infrequent workloads before removing apparently unused indexes.

Key points to remember

  • B-tree indexes provide ordered access; total query cost also includes matching entries and row fetches.
  • Indexes can accelerate filters, joins, sorting, grouping and covering queries when their structure matches the query.
  • Additional indexes consume storage and cache space and increase write-maintenance costs.
  • Composite-column order, predicate selectivity and sargability affect whether efficient index access is possible.
  • Choose indexes from real workloads and verify plans and timings rather than assuming an index will help.

Common mistakes candidates make

  • Claiming every indexed query takes O(log n), regardless of result size or table lookups.
  • Treating low-cardinality indexes as always useless or the leftmost-prefix rule as having no engine-specific exceptions.
  • Indexing every column or routinely rebuilding indexes without measuring benefits, write costs and maintenance needs.

Likely follow-up questions

Why might the optimiser ignore an existing index?

A scan may be cheaper when the query returns a large fraction of the table or the table is small. Stale statistics, incompatible expressions or unsuitable composite-column ordering can also prevent an efficient index access path.

How would you index a customer's latest 20 orders?

For WHERE customer_id = ? ORDER BY order_date DESC LIMIT 20, consider an index on (customer_id, order_date DESC). It can locate that customer's entries and read them in the required order, stopping after 20 qualifying rows; confirm the behaviour with the execution plan.

What is a covering index, and what does it cost?

A covering index contains all columns required for a particular query, allowing the engine to potentially answer it without fetching table rows. Adding payload columns widens the index and increases storage and maintenance costs; some engines support INCLUDE columns that do not participate in key ordering.

Interview tip: Start with the access-path explanation, illustrate one composite index, then balance read benefits against write, storage and maintenance costs.

Related Interview Questions

1
SQLAdvanced#indexes

Clustered vs non-clustered index?

Open
2
SQLAdvanced#indexes#performance

What is a covering index?

Open
3
SQLAdvanced#indexes#performance

Why might an index not be used by a query?

Open
More SQL interview questions →