SQL Indexes and Query Plans: Making Slow Queries Fast

A query that returns instantly on your laptop can take seconds in production. Usually nothing is wrong with the query itself: the table just grew from a thousand rows to a few million, and the database is reading every single one of them to find the handful you asked for.

Indexes fix that, but only the right index, and only if your query lets the database use it. In this article you'll build a million-row table, watch a query go from 100 ms to under a millisecond, and learn to read the query plan that tells you why.

We'll use SQLite because it's already installed almost everywhere and needs no server. The ideas carry over directly to PostgreSQL and MySQL; there's a translation table at the end. If JOINs are still new to you, start with SQL JOINs Explained with Simple Examples.

Set up a million rows#

Open a new database with sqlite3 shop.db and paste this in. It creates an orders table and fills it with one million random orders from 50,000 customers:

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL,
  total       REAL    NOT NULL,
  created_at  TEXT    NOT NULL
);

WITH RECURSIVE n(i) AS (
  SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000000
)
INSERT INTO orders (customer_id, status, total, created_at)
SELECT abs(random()) % 50000 + 1,
       CASE abs(random()) % 10 WHEN 0 THEN 'pending' WHEN 1 THEN 'cancelled' ELSE 'shipped' END,
       (abs(random()) % 50000) / 100.0,
       datetime('2024-01-01', '+' || (abs(random()) % 86400000) || ' seconds')
FROM n;

Then turn on timing with .timer on. Every query will now report how long it took.

Reading a query plan#

Prefix any query with EXPLAIN QUERY PLAN and SQLite shows its strategy instead of running it:

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

QUERY PLAN
`--SCAN orders

SCAN means a full table scan: SQLite reads all one million rows and checks each one. Run the query itself and, in our test, it took about 100 ms to return a handful of rows. That doesn't sound like much until a page runs it twenty times, or a thousand users load that page at once.

How a B-tree index works#

An index is a separate, sorted copy of one or more columns, where each entry points back to its row. Databases store it as a B-tree: a shallow tree of pages, each holding hundreds of sorted keys. To find 4242, the database reads the root page, follows the pointer for the range containing 4242, and repeats until it reaches a leaf page. Because each page fans out to hundreds of children, a million rows need only three or four levels.

Create one and look at the plan again:

CREATE INDEX idx_orders_customer ON orders (customer_id);

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

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SEARCH ... USING INDEX means SQLite walks the index to the matching entries, then fetches just those rows from the table. The same query now runs in under a millisecond, roughly a hundred times faster, and the gap keeps growing with the table: a scan doubles in cost when the table doubles, while an index lookup barely notices.

Composite indexes: column order is everything#

Real queries filter on more than one column. Here's a typical one: a customer's recent orders, newest last.

SELECT * FROM orders
WHERE customer_id = 4242 AND created_at >= '2025-01-01'
ORDER BY created_at;

With only idx_orders_customer, the plan has a second line:

QUERY PLAN
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

USE TEMP B-TREE FOR ORDER BY means the rows come back unsorted and SQLite has to sort them itself. A composite index on both columns solves the filter and the sort at once:

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_created (customer_id=? AND created_at>?)

Think of a composite index like a phone book sorted by last name, then first name. Inside the "4242" section, entries are already sorted by created_at, so the database jumps to the first date it needs and reads forward. No sort step.

Now try the columns the other way round. Drop the two customer indexes first, so SQLite can't pick them instead:

DROP INDEX idx_orders_customer;
DROP INDEX idx_orders_customer_created;
CREATE INDEX idx_orders_created_customer ON orders (created_at, customer_id);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_created_customer (created_at>?)

It still says SEARCH, so it looks fine. But it took 95 ms in our test, almost as slow as no index at all. Look inside the parentheses: only created_at>? uses the index. The range covers about two thirds of all orders, and SQLite has to check every one of those entries for customer_id = 4242. Drop this index (DROP INDEX idx_orders_created_customer;) before moving on.

Covering indexes: skip the table entirely#

Even with a perfect index, each match costs an extra jump from the index into the table to fetch the rest of the row. If the index already contains every column the query needs, the database can skip the table altogether:

CREATE INDEX idx_orders_customer_created_total
  ON orders (customer_id, created_at, total);

EXPLAIN QUERY PLAN
SELECT created_at, total FROM orders WHERE customer_id = 4242;

QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_customer_created_total (customer_id=?)

COVERING INDEX is the best plan you can get for a lookup. It matters most for queries that return many rows, such as reports and dashboards. It's another good reason to avoid SELECT *: asking for fewer columns makes a covering index possible.

Mistakes that silently disable an index#

An index only helps if the database can compare your condition directly against the sorted values. Wrap the column in a function or expression, and it can't. Say we add a plain index on created_at and look for one day's orders:

CREATE INDEX idx_orders_created ON orders (created_at);

-- ✗ a full SCAN: date() has to be computed for every one of the million entries (≈600 ms)
SELECT count(*) FROM orders WHERE date(created_at) = '2025-03-14';

-- ✓ a SEARCH on idx_orders_created (created_at>? AND created_at<?) (<1 ms)
SELECT count(*) FROM orders
WHERE created_at >= '2025-03-14' AND created_at < '2025-03-15';

Both return the same count. The second form is called sargable (from Search ARGument ABLE): the column stands alone on one side of the comparison. Other common index killers:

When you genuinely need to filter on an expression, index the expression itself. SQLite, PostgreSQL and MySQL 8 all support this:

CREATE INDEX idx_orders_day ON orders (date(created_at));
-- now WHERE date(created_at) = '2025-03-14' uses: SEARCH orders USING INDEX idx_orders_day (<expr>=?)

Partial indexes: index only the rows you query#

Suppose a background job keeps asking for pending orders, which are only 10% of the table. A partial index includes just those rows:

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE status = 'pending' AND created_at < '2024-02-01';

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_pending (created_at<?)

In our database this index takes 2.7 MB, against 27 MB for a full index on created_at. Smaller indexes are faster to search, fit in memory more easily, and cost less to update. PostgreSQL supports partial indexes too; MySQL doesn't.

Indexes aren't free#

Every index is a second data structure the database must keep sorted. Each INSERT, UPDATE of an indexed column, and DELETE has to update every index on the table. In our test, inserting 200,000 more rows into the million-row table took about half a second with no secondary indexes and over seven seconds with three of them.

So don't index every column "just in case":

• Index columns that appear in frequent WHERE, JOIN and ORDER BY clauses.
• Prefer one well-designed composite index over several single-column ones.
• Look for redundant indexes: (customer_id) is redundant once (customer_id, created_at) exists, because of the leftmost prefix rule.
• Columns with only a few distinct values (like status) rarely make good indexes on their own. Use them in a partial index or as a later column in a composite one.
• After big data changes, run ANALYZE so the planner has up-to-date statistics to choose between indexes.

Reading plans in PostgreSQL and MySQL#

The vocabulary differs, but the questions are the same: is it scanning or searching, which index is used, and is there an extra sort?

In PostgreSQL, use EXPLAIN ANALYZE to actually run the query and see real row counts and timings next to the planner's estimates. When an estimate is wildly off from the real count, outdated statistics are the usual suspect.

Recap#

• SCAN reads every row; SEARCH ... USING INDEX jumps straight to the matches through a sorted B-tree.
• In composite indexes, put equality columns first, then one range or sort column. Indexes only serve their leftmost prefix.
• A covering index answers the query without touching the table.
• Functions and arithmetic on an indexed column disable the index. Rewrite the condition, or index the expression.
• Partial indexes keep indexes small when you only query a subset of rows.
• Every index slows down writes. Add indexes for real queries, and check the plan before and after.

The habit that matters most: before you add an index, run EXPLAIN. After you add it, run EXPLAIN again.