SQL · Lesson 7 of 7

Indexes and Query Performance

Why queries are slow, how indexes help and how to read EXPLAIN.

• Advanced
• 14 min read
• 3 objectives

Before this lessonLesson 6: Creating Tables and Design

A query that takes 2 ms on a hundred rows can take minutes on a hundred million. Without help, the database must read every row to find matches, a full table scan. An index is a separate, sorted lookup structure (usually a B-tree) that lets it jump straight to the right rows, like the index at the back of a book.

Why queries get slow

With 100 rows every query feels instant, whatever you write. With 100 million rows, the way the database finds data matters enormously. Without help it must read every row to find matches, a full table scan, like finding a name by reading a phone book from page one. An index is the alphabetical order that lets it jump straight to the right page.

Creating an index

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);   -- composite

Primary keys and UNIQUE constraints are indexed automatically. Foreign key columns usually are not, and they are the most common candidates: joins and lookups on them are constant companions.

Reading the plan

EXPLAIN shows how the database intends to run a query. Look for a scan of the whole table versus an index search.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

-- before index: SCAN orders
-- after index:  SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

In PostgreSQL use EXPLAIN ANALYZE to also run the query and report real timings.

The trade-off

• Indexes make reads faster but every INSERT, UPDATE and DELETE must also maintain them, so writes get slower.
• They use disk space. Do not index everything; index columns you filter, join and sort on.
• Low-selectivity columns (such as a boolean) rarely benefit.

Composite index order

An index on (customer_id, status) helps queries filtering by customer_id, or by both, but not by status alone. Put the most commonly filtered, most selective column first.

Patterns that defeat indexes

-- Slow: function on the column prevents index use
SELECT * FROM customers WHERE LOWER(email) = 'a@b.com';

-- Slow: leading wildcard
SELECT * FROM customers WHERE name LIKE '%son';

-- Better: compare the raw column
SELECT * FROM customers WHERE email = 'a@b.com';

Other habits that keep queries fast

• Select only needed columns instead of SELECT *.
• Paginate with LIMIT; for deep pages prefer keyset pagination (WHERE id > last_seen) over big OFFSETs.
• Avoid the N+1 problem: one query per row from application code. Fetch related rows with a join or an IN list.

See the difference with EXPLAIN

Databases can describe their plan for a query. Below, the same search is run before and after adding an index. The plan changes from a scan of the whole table to a direct search through the index.

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'ada@example.com'

SCAN users

CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'ada@example.com';

SEARCH users USING INDEX idx_users_email (email=?)

SCAN means "read everything"; SEARCH ... USING INDEX means "jump to the match." The words differ between databases (PostgreSQL says Seq Scan and Index Scan) but the idea is identical.

What to index

• Columns you filter on often in WHERE (email, status, created_at).
• Foreign-key columns used in joins (orders.customer_id).
• Columns you sort by frequently.

Indexes are not free

Every index takes disk space and must be updated on each INSERT, UPDATE and DELETE. Too many indexes make writes slow. Add them for real, measured slow queries, not on every column just in case.

A tuning routine that works

• Measure first: find which query is actually slow (most databases have a slow-query log).
• Explain it: does the plan show a full scan on a big table?
• Fix the cause: add an index, select fewer columns, or rewrite the filter so the index can be used.
• Measure again: confirm the plan and the time both improved.

Key takeaways

• Without an index the database scans every row; an index lets it jump to matches.
• Use EXPLAIN to see whether a query scans or searches an index.
• Index columns you filter, join and sort on; avoid indexing everything.
• Measure before and after; guessing is not tuning.

-- Write your solution here