# SQL Indexes

> A SQL index is a separate data structure, usually a B-tree, that the database maintains alongside a table to make lookups on certain columns fast. Instead of a full table scan, the database can jump almost directly to matching rows, at the cost of extra storage and slower writes, since every index must be updated too.

Canonical: https://devsunite.com/resources/core-subjects/dbms/sql-indexes
Last updated: 2026-11-22

Subject: DBMS · Category: SQL · 3 min read

An index is an extra, separate data structure the database maintains alongside a table, specifically to make lookups on certain columns fast. Without an index, finding rows that match a condition means scanning every single row in the table, a full table scan. With the right index, the database can jump almost directly to the matching rows instead.

## The analogy that actually explains it

A book's index at the back lets you find every page mentioning a specific topic without reading the entire book cover to cover. A database index works the same way: it's a separate, sorted structure (usually a B-tree) mapping column values to the rows that contain them, so a lookup can use that structure instead of checking every row.

## A worked example

A `users` table with 5 million rows, and a common query looking up a user by email:

```sql
SELECT * FROM users WHERE email = 'someone@example.com';
```

Without an index on `email`, the database has to check all 5 million rows one by one to find matches, a full table scan. Adding an index changes that entirely:

```sql
CREATE INDEX idx_users_email ON users (email);
```

Now that same query can use the index's sorted structure to jump almost directly to the matching row, instead of scanning millions of rows, a difference that can be the gap between a query taking milliseconds versus seconds on a large table.

## The real tradeoff: indexes aren't free

Every index the database maintains has to be updated whenever a row is inserted, updated, or deleted, since the index needs to stay in sync with the actual data. This means indexes speed up reads but slow down writes, and they take up additional disk space. A table with too many indexes on rarely-queried columns can suffer real, unnecessary write performance cost for no read benefit, which is why indexes are added deliberately, based on actual query patterns, not applied to every column by default.

## Where this matters in real systems

Real production database performance problems are extremely often traced back to a missing index on a column that gets filtered or joined on constantly, a query that used to run fine on a small table starts taking seconds once the table grows to millions of rows, and adding the right index fixes it immediately. Database administrators regularly use a query's execution plan (`EXPLAIN` in PostgreSQL/MySQL) to see whether a slow query is doing a full table scan and would benefit from an index.

## What interviewers actually ask about SQL indexes

- **"What is an index, and why does it help?"** A separate sorted data structure that lets the database find matching rows without scanning the entire table.
- **"What's the tradeoff of adding an index?"** Faster reads on the indexed column, but slower writes (since the index must be kept in sync) and additional storage space.
- **"When would you NOT want to add an index?"** On columns rarely used in WHERE clauses or joins, or on tables with very heavy write traffic where the write-speed cost outweighs the read benefit.
- **"How would you check if a slow query is missing an index?"** Run EXPLAIN (or EXPLAIN ANALYZE) on the query to see its execution plan and check whether it's doing a full table scan.

## Key takeaways

- An index is a separate structure that lets the database find matching rows quickly, without a full table scan.
- Indexes speed up reads but slow down writes and use extra storage, since they must stay in sync with the table.
- Indexes should be added deliberately, based on real, frequent query patterns, not applied to every column.
- EXPLAIN/EXPLAIN ANALYZE is the standard way to check whether a slow query would benefit from an index.

## Related

- [Previous: SQL Views](https://devsunite.com/resources/core-subjects/dbms/sql-views)
- [Next: SQL Subqueries](https://devsunite.com/resources/core-subjects/dbms/sql-subqueries)
- [More in SQL: Introduction to SQL](https://devsunite.com/resources/core-subjects/dbms/introduction-to-sql)
- [More in SQL: SQL Data Types](https://devsunite.com/resources/core-subjects/dbms/sql-datatypes)
- [More in SQL: Basic SQL Commands](https://devsunite.com/resources/core-subjects/dbms/basic-sql-commands)
- [More in SQL: Other SQL Commands](https://devsunite.com/resources/core-subjects/dbms/other-sql-commands)
- [More in SQL: SQL Aggregate Functions](https://devsunite.com/resources/core-subjects/dbms/aggregate-functions)
- [More in SQL: SQL Joins](https://devsunite.com/resources/core-subjects/dbms/sql-joins)
- [More in SQL: SQL Stored Procedures](https://devsunite.com/resources/core-subjects/dbms/sql-stored-procedures)
- [All 174 DBMS interview questions](https://devsunite.com/resources/core-subjects/dbms)
