Indexes and EXPLAIN

Why it matters

On a table of 1,000 rows every query is instant. On 100 million rows, the same query can take minutes, time out a dashboard or slow down the application that shares the database. Analysts and DBAs do not need to know every detail of a database engine, but they do need to know what an index is, when it helps, and how to read a query plan to see whether the database is using one.

What an index is

Think of the index at the back of a textbook. To find every page about "photosynthesis" you do not read the whole book; you look the word up in a sorted list that points to page numbers. A database index is the same idea: a separate, sorted structure (usually a B-tree) holding the values of one or more columns plus a pointer to each matching row.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Without that index, WHERE customer_id = 42 makes the database read every row (a full table scan). With it, the database jumps straight to the 42s, reading a tiny fraction of the table. Most databases automatically index primary keys and columns with a UNIQUE constraint.

Reading a query plan

You ask the database how it plans to run a query, without running it, using EXPLAIN. In SQLite the readable form is EXPLAIN QUERY PLAN:

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

Before the index:

SCAN orders

After CREATE INDEX idx_orders_customer ON orders (customer_id):

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

In SQLite plans:

• SCAN means it reads the whole table (or the whole index) from start to finish.
• SEARCH means it uses an index or the primary key to jump to matching rows.
• USING COVERING INDEX means the index alone contains every column the query needs, so the table itself is not read at all: the fastest case.
• USE TEMP B-TREE FOR ORDER BY (or GROUP BY) means it has to sort rows itself because no index provides that order.

Other databases show more detail. PostgreSQL's EXPLAIN prints nodes such as Seq Scan (full scan), Index Scan and Bitmap Heap Scan, with estimated costs and row counts; EXPLAIN ANALYZE actually runs the query and shows real times. MySQL has EXPLAIN with a type column (ALL = full scan, ref/range = index use). SQL Server shows graphical execution plans. The vocabulary differs, but the main question is always the same: is it scanning everything, or using an index to read only what it needs?

When an index can be used

An index on (customer_id) helps conditions that compare the bare column:

WHERE customer_id = 42                -- can use the index
WHERE customer_id IN (1, 2, 3)        -- can use the index
WHERE customer_id + 0 = 42            -- cannot: the column is inside an expression

The same applies to functions: WHERE substr(order_date, 1, 4) = '2026' cannot use a plain index on order_date, but the equivalent range WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' can. Rewriting filters as ranges on the raw column is one of the most useful habits for fast analytical SQL. Similarly, LIKE 'abc%' (prefix) can sometimes use an index, while LIKE '%abc' cannot.

Composite indexes and column order

An index can cover several columns: CREATE INDEX idx_cust_date ON orders (customer_id, order_date). It is sorted by customer_id first, then order_date within each customer, like a phone book sorted by last name then first name. So it helps:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND order_date >= '2026-01-01'

but generally not WHERE order_date >= '2026-01-01' alone, just as a phone book does not help you find everyone named "Sam". Put the column you filter with = most often first.

Indexes are not free

Every index:

• takes disk space, sometimes as much as the table;
• must be updated on every INSERT, UPDATE and DELETE, which slows writes;
• gives the planner more choices, and a bad index can occasionally be picked.

So you do not index every column. You index columns that appear often in WHERE, JOIN ... ON and ORDER BY of important queries, on tables big enough to matter. For a very small table, or a condition that matches most of the rows (say status = 'shipped' when 95% are shipped), a full scan is often faster and the planner will rightly ignore the index.

Analysts and indexes in practice

In many companies analysts query a data warehouse (BigQuery, Snowflake, Redshift, Databricks). These use columnar storage, partitioning and clustering instead of classic B-tree indexes, so the advice becomes "filter on the partition column (usually a date) and select only the columns you need". In an operational database (PostgreSQL, MySQL, SQL Server), adding indexes is usually the DBA's or developer's job: an analyst's role is to notice a slow query, read its plan, and ask for the right index with evidence.

Quick recap

• An index is a sorted lookup structure that lets the database find rows without scanning the table.
• EXPLAIN QUERY PLAN (SQLite) or EXPLAIN (PostgreSQL, MySQL) shows whether a query scans or searches.
• Compare bare columns or ranges; wrapping a column in a function or expression usually prevents index use.
• Composite indexes work left to right. Indexes speed reads and slow writes.

Check yourself

This lesson has no coding task. Answer the questions in the Do tab, then try EXPLAIN QUERY PLAN on any query from earlier lessons in a SQLite tool such as DB Browser for SQLite or the sqlite3 command line.