Indexes — SQL Tutorial with Examples

What You'll Learn

An index is a data structure that speeds up reads by letting the DB find rows without scanning the full table — like a book index. Clustered Index: physically reorders the table data (one per table, usually the Primary Key). Non-Clustered Index: separate structure with pointers to rows (multiple allowed). Indexes speed up reads but slow down writes.

Syntax

CREATE INDEX idx_name ON table(col);
CREATE UNIQUE INDEX idx ON table(col);
CREATE INDEX idx ON table(col1, col2);
DROP INDEX idx_name ON table;
EXPLAIN SELECT ...;

Example

-- Index on a frequently filtered column
CREATE INDEX idx_cust_email ON customers(email);

-- Composite index for the classic query pattern
CREATE INDEX idx_cust_date
ON orders(customer_id, order_date);

-- Check if a query actually uses the index
EXPLAIN SELECT * FROM customers
WHERE email = 'karl@example.com';
-- Look for 'type: ref' and 'key: idx_cust_email'

Beyond the Basics

Data in play — reference tables for this topic

-- customers (from Topic 6) — email is high-cardinality: index it
-- email: karl@, ines@, omar@, sofia@, maike@example.com

-- orders (from Topic 7) — filtered by customer_id + order_date
-- id | customer_id | total  | order_date
-- 1  | 1           | 240.00 | 2024-01-15
-- 2  | 2           | 89.90  | 2024-02-03
-- 3  | 1           | 430.00 | 2024-03-11
-- 4  | 3           | 59.50  | 2024-04-02
-- 5  | 4           | 120.00 | 2024-05-20

What an index actually is — a sorted copy

idx_cust_email is a B-tree: a sorted copy of every email, plus a pointer to its row. Finding 'karl@example.com' becomes binary search — ~20 hops in a million-row index instead of a million row reads. The same sortedness explains a bonus you get for free: ORDER BY email needs no sort at all, it just reads the index in order.

-- Seek, not scan:
SELECT * FROM customers WHERE email = 'karl@example.com';
-- B-tree: compare 3 times in our 5-row index, ~20 in a million

-- Free sort (the index is already ordered):
SELECT * FROM customers ORDER BY email;

An index is a second copy of one column, kept sorted — that's why reads fly, writes pay (later), and sorted output is free.

Composite indexes and the leftmost-prefix rule

idx_cust_date (customer_id, order_date) sorts first by customer, then by date within each customer. That ordering serves three query shapes: customer alone, customer + date — but NOT date alone, because dates are scattered across customers in the index. Column order is the design decision; there is no 'partial' use of the second column.

-- Uses the index (customer_id is leftmost):
SELECT * FROM orders WHERE customer_id = 1;
-- Uses it fully (left column seeks, right column ranges within):
SELECT * FROM orders
WHERE customer_id = 1 AND order_date >= '2024-01-01';

-- Full scan — order_date is not the leftmost column:
SELECT * FROM orders WHERE order_date >= '2024-01-01';

Equality columns first, range column last, most-selective first — a composite index is one tool shaped by its column order, not a bag of shortcuts.

The write tax — and the low-cardinality trap

Every INSERT, UPDATE, or DELETE of an indexed column must also update every index containing it. Index five columns and writes cost six. Worse, some indexes buy nothing: orders.status has three values (shipped, pending, cancelled) — a B-tree over it narrows a million-row table to ~333k-row ranges, which the engine may deem not worth seeking.

-- Weak index: 3 distinct values across millions of rows
CREATE INDEX idx_ord_status ON orders(status);   -- dubious value

-- Strong index: near-unique values
CREATE INDEX idx_cust_email ON customers(email); -- worth it

-- Every UPDATE below also updates BOTH indexes:
UPDATE orders SET status = 'shipped' WHERE id = 3;

Index for selectivity: high-cardinality columns and join keys earn their write tax; low-cardinality flags usually don't (a covering or partial index is the specialist's fix).

Common Mistakes

Over-indexing: every index slows down INSERT/UPDATE/DELETE because the index must also be updated. Don't index low-cardinality columns (e.g., boolean, gender). Applying functions to indexed columns in WHERE (YEAR(date), LOWER(email)) disables the index.

Interview Tips

Index columns used in WHERE, JOIN ON, and ORDER BY with high cardinality. Composite indexes: column order matters — (customer_id, date) serves queries on customer_id alone but NOT queries on date alone. Explain EXPLAIN output: type=ALL = full scan (bad), type=ref = index used (good).

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. What does CREATE INDEX actually build?

2. Index idx_cust_date ON orders(customer_id, order_date) — which query CANNOT use it?

3. Why does every index slow down INSERT/UPDATE/DELETE?

4. Why is an index on orders.status of limited value?

5. Clustered vs non-clustered index — what's the core difference?

6. Where do the sargability warnings from Topics 3, 12, and 14 collide with indexes?

Practice

Return every column of the employee with id = 5 — the lookup a primary-key index resolves instantly.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Clustered vs non-clustered index — what's the difference?

The clustered index IS the table — its leaf nodes are the actual rows, ordered (one per table, typically the primary key). Non-clustered indexes are separate structures whose leaves point to those rows. That's why a primary-key lookup is the fastest path in most engines, and why secondary index lookups cost an extra hop to the row.

How do I find indexes nobody is using?

The engines track usage statistics: pg_stat_user_indexes in PostgreSQL (idx_scan count), sys.schema_unused_indexes in MySQL. An index with scans near zero is pure write tax — drop it. Audit quarterly: schemas accumulate indexes the way attics accumulate boxes.