Start Learning Contact Us
SQL for Data Analysis

Indexes and Query Performance

Lesson 12 of 13

On six rows every query is instant. On sixty million, the way a query is written and the indexes available decide whether it takes a millisecond or an hour.

Full scans

Without help, the database finds matching rows by reading every row in the table. This is a full table scan, and its cost grows with the size of the table.

Indexes

An index is a sorted copy of one or more columns with pointers back to the rows, like the index at the back of a book. The database can jump to the matching entries without reading the whole table.

CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT order_id, order_date
FROM orders
WHERE customer_id = 1;

A primary key is indexed automatically.

Seeing the plan

EXPLAIN QUERY PLAN shows how SQLite will run a query without running it. Look at the detail column.

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

The plan says SCAN orders: a full scan. Now create the index first:

CREATE INDEX idx_orders_customer ON orders (customer_id);

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

The plan changes to SEARCH orders USING INDEX. Other databases have the same feature under the name EXPLAIN or EXPLAIN ANALYZE.

What to index

  • Columns used in WHERE conditions on large tables.
  • Columns used to join tables, especially foreign keys.
  • Columns used in ORDER BY when sorting is slow.

The cost of an index

An index takes storage, and it must be updated on every insert, update and delete. Index the columns your queries actually filter and join on, not every column.

Multi-column indexes

An index on several columns is sorted by the first, then the second, and so on. It helps queries that filter on the first column, or on the first and second, but not on the second alone.

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

EXPLAIN QUERY PLAN
SELECT order_id FROM orders
WHERE status = 'delivered' AND order_date >= '2025-04-01';

Writing queries that can use an index

Applying a function to an indexed column usually prevents the index from being used.

CREATE INDEX idx_orders_date ON orders (order_date);

-- cannot use the index: the column is wrapped in a function
EXPLAIN QUERY PLAN
SELECT order_id FROM orders WHERE strftime('%Y', order_date) = '2025';

-- can use the index: the column is compared directly
EXPLAIN QUERY PLAN
SELECT order_id FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

A LIKE pattern that starts with % cannot use an ordinary index either.

Habits that keep queries fast

  • Select only the columns you need. SELECT * reads and transfers more data.
  • Filter as early as possible, so that later steps handle fewer rows.
  • Check that every join has an ON condition. A missing one produces every combination of rows.
  • Use EXISTS to test for related rows when you do not need their columns.
  • Use UNION ALL when you do not need duplicates removed.
  • Add LIMIT while you explore.
  • Aggregate before joining when you can, so that the join handles fewer rows.

Practice

Use EXPLAIN QUERY PLAN on a query that finds order items for product 1. Create an index on order_items (product_id) and compare the plans.