Professional level · Lesson 17 of 20

Indexes: why is my query slow?

How the database searches, EXPLAIN, and when an index hurts.

11 minutes1 games

Without an index, to find Cairo students the database reads every row one by one (a full table scan). With 8 rows it doesn't matter; with 10 million the query takes seconds.

An index is like the index at the back of a book: a sorted list of values, each pointing to its rows. The database jumps straight there instead of turning every page.

index on city (B-tree)
- A … C
  - Alexandria → rows 2, 5
  - Cairo → rows 1, 3, 6
- G … M
  - Giza → rows 4, 8
  - Mansoura → row 7

Most indexes are B-trees: values are sorted, so a lookup takes few steps even on a huge table.

EXPLAIN QUERY PLAN (in PostgreSQL: EXPLAIN) tells you what the database plans to do. Compare the plan before and after the index:

SQL

EXPLAIN QUERY PLAN
SELECT name FROM students WHERE city = 'Cairo';

CREATE INDEX idx_students_city ON students (city);

EXPLAIN QUERY PLAN
SELECT name FROM students WHERE city = 'Cairo';

SCAN means it read the whole table; SEARCH … USING INDEX means it used the index. In PostgreSQL you'll see Seq Scan versus Index Scan.

Composite indexes and column order

An index on (city, xp) is sorted by city first, then by XP within each city, like a phone book (surname, then first name). It helps WHERE city = 'Cairo' and WHERE city = 'Cairo' AND xp > 100, but not WHERE xp > 100 alone, just as a phone book can't help you search by first name only.

SQL

CREATE INDEX idx_city_xp ON students (city, xp);

EXPLAIN QUERY PLAN
SELECT name FROM students WHERE city = 'Cairo' AND xp > 100;

EXPLAIN QUERY PLAN
SELECT name FROM students WHERE xp > 100;

Quick question

You have an index on (city, xp). Which query will not benefit from it?

Learn more from

Played 0 of 1 games

SQL