Understanding SQL Indexes and Query Performance

Part 1 of 16 · ~2 min

Overview

Somebody in your company is sitting and watching a spinner right now, and it is your fault.

Here is the situation. You maintain an internal tool — nothing glamorous, a single screen that HR calls the pay-band screen. You pick a department, you pick a salary range, and it lists everyone who matches, sorted by salary. It is used all year at a trickle and then, for about ten days each quarter during compensation review, it is hammered: two dozen managers with the tab permanently open, each one clicking a different department, waiting, clicking another.

The tables behind it are the two you already know from the code lab:

CREATE TABLE Departments (
  dept_id INTEGER PRIMARY KEY,
  dept TEXT NOT NULL
);

CREATE TABLE Employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER
);

In the code lab that is five employees across four departments, which you can query instantly no matter what you do to it. In the production database it is the same two tables with the same four columns and 2.4 million employee rows across 38 departments, and what you do to it matters enormously. That gap is the whole point: every statement in this reference runs, verbatim, in the code lab — but the timings and the decisions come from the large version, because indexes are a subject that only becomes visible at scale.

One more thing about that production data, because it drives several chapters: the departments are wildly uneven. Sales has about 431,000 people in it. Facilities has about 6,200. The same query shape against those two values will make the database behave in genuinely different ways, and by the end you will be able to predict which.

Here is the query the pay-band screen runs. It is the single most important sixty characters in this entire reference, because every chapter from here is about making this exact statement faster:

SELECT name, dept, salary
FROM Employees
WHERE dept = 'Sales' AND salary BETWEEN 60000 AND 90000
ORDER BY salary;

Right now, on 2.4 million rows, that takes about four seconds. By the end of this reference it will take about four milliseconds, and — this is the part nobody tells you — the annual raise run that used to finish before breakfast will have gotten measurably slower as a direct result. Both halves of that trade are the subject here.

If none of this is familiar yet, go chapter by chapter — the pay-band screen genuinely accumulates: the index we build in chapter 4 gets rebuilt in chapter 5 because chapter 5 proves it was wrong, and chapter 13 sends you back to delete two we added earlier. Already comfortable with the material? The sidebar will drop you straight into whichever piece you need — each chapter names which version of the screen it is working on, so you can pick up the thread from anywhere.