MediumConcept

What is an index in SQL and how does it improve performance?

Technology:
SQL
Experience:
Mid-level,
Senior

Quick answer

An index is a separate sorted data structure (usually a B-tree) that lets the database find rows without scanning the whole table, at the cost of extra storage and slower writes.

Detailed explanation

Without an index, a query like WHERE email = 'a@b.com' reads every row (a full table scan). With an index on email the database walks the tree in a handful of steps and jumps straight to the matching rows. Indexes also speed up JOIN, ORDER BY and GROUP BY on the indexed columns.

Every INSERT, UPDATE and DELETE must also update every index on the table, so do not index everything. In a composite index the column order matters: an index on (last_name, first_name) helps a search on last_name alone but not on first_name alone. An index is also skipped when you wrap the column in a function (WHERE LOWER(email) = ...) or use a leading wildcard (LIKE '%smith'), and it is of little use on low-cardinality columns such as a boolean.

Example

sql
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

-- uses the index
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2026-01-01';
-- cannot use it efficiently (function on the column)
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';

How to explain it in an interview

An index is a separate sorted structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up reads that filter, join or sort on the indexed columns, but every insert and update must also maintain the index, so I add indexes for real query patterns and check the execution plan.

Key points

  • Faster reads, slower writes, more storage
  • Column order in composite indexes matters
  • Functions on the column and leading wildcards prevent index use

Common mistakes

  • Indexing every column. Each index costs write time and storage, and the optimizer may ignore most of them.
  • Wrapping an indexed column in a function, such as WHERE LOWER(email) = ..., which stops a normal index from being used.
  • Ignoring column order in a composite index. An index on (a, b) helps queries on a, but not queries on b alone.

Follow-up questions

  • What is a covering index?
  • How do you decide which columns to index?
  • SQLMedium

    What is database normalization and what are the normal forms?

    Normalization is organising tables to reduce redundancy and update anomalies: 1NF means atomic values, 2NF removes partial dependencies on part of a composite key, and 3NF removes dependencies between non-key columns.

    Junior 路 Mid-level 路 Database
  • SQLMedium

    What are ACID properties in a database?

    ACID stands for Atomicity, Consistency, Isolation and Durability: a transaction either fully succeeds or fully fails, keeps the data valid, does not interfere with other transactions, and survives crashes once committed.

    Mid-level 路 Senior 路 Database
  • System DesignMedium

    When should you use a SQL database vs a NoSQL database?

    Choose SQL when your data is relational and you need transactions, joins and strong consistency; choose NoSQL when you need flexible schemas, very high write throughput or horizontal scaling for a specific access pattern.

    Mid-level 路 Senior 路 Database
  • SQLHard

    How do you optimize a slow SQL query?

    Read the execution plan with EXPLAIN to find full scans and expensive joins, add or fix indexes for the filter and join columns, select only the columns and rows you need, and rewrite predicates so indexes can be used.

    Senior 路 Performance