Indexes and Faster Queries: EXPLAIN for Beginners

On a table with eight rows, every query is instant. On a table with ten million rows, the same query can take minutes if it is written or indexed badly. You do not need to be a database expert to write fast queries, but you should understand one idea: the index. This lesson explains it with a simple picture, and shows how to check whether a query uses one.

What is an index?

Think about a textbook. If you want the page about “joins”, you can read every page from the start, or you can look in the index at the back, which lists topics in order with page numbers, and jump straight there. A database index is the same. It is a separate sorted structure that remembers where rows are, so the database can jump to the matching rows instead of reading the whole table.

Reading the whole table is called a full table scan. Using an index to jump is an index search or seek. On big tables, the difference can be a thousand times.

See the plan: EXPLAIN

You can ask the database how it will run a query, without running it. In SQLite the command is EXPLAIN QUERY PLAN. PostgreSQL and MySQL use EXPLAIN, and SQL Server shows an execution plan in its tools.

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

SCAN orders

SCAN orders means the database will look at every row of orders to find the ones for customer 1. With ten rows, who cares. With ten million, this is the problem.

Create an index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

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

SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

Now the plan says SEARCH ... USING INDEX. The database jumps straight to the right rows. The words you see differ in other engines (PostgreSQL says “Index Scan” or “Seq Scan”, MySQL shows type: ref or ALL), but the idea is the same: look for the words that mean “whole table” and the words that mean “used an index”.

What to index

• Columns in `WHERE` that you filter on often, especially on big tables.
• Foreign key columns you join on, such as orders.customer_id and order_items.order_id. Joins on unindexed columns are a classic cause of slow reports. Primary keys are indexed automatically. Some engines, such as PostgreSQL, do not index foreign keys for you.
• Columns in `ORDER BY` for big sorted lists.
• A unique index (CREATE UNIQUE INDEX) also enforces that values do not repeat. A UNIQUE constraint creates one.

You can index several columns together, for example (customer_id, order_date). Order matters: it helps queries that filter on customer_id, or on both, but not on order_date alone.

Indexes are not free

Every index takes disk space, and every INSERT, UPDATE and DELETE must also update it. Too many indexes slow writes down. So you do not index every column. You index the ones your real queries use on large tables. On small tables, an index can even be slower than a scan, and the database may ignore it.

Habits that stop an index being used

• A function on the column: WHERE UPPER(name) = 'ASHA' or WHERE SUBSTR(order_date, 1, 7) = '2024-06' cannot use a normal index on that column, because the database would need to compute the function for every row. Rewrite as a range: order_date >= '2024-06-01' AND order_date < '2024-07-01'.
• A leading wildcard: LIKE '%son' cannot use an ordinary index. LIKE 'Ash%' can.
• A type mismatch: comparing a number column with text can stop the index from working in some engines.
• **SELECT * on wide tables** moves more data than you need. Name the columns.
• No limit when you only need a few rows. Add LIMIT.

Other habits for faster queries

• Filter early: put selective WHERE conditions in so that fewer rows enter the joins.
• Join on keys, which are usually indexed.
• Avoid running the same query in a loop from a program for each row, which is a very common cause of slow pages. Ask for all the rows in one query with a join or IN.
• Do not guess. Test with EXPLAIN and measure with real data volumes. A query tested on ten rows tells you nothing about ten million.

When a report is still slow

In a real job you will sometimes hit a slow query, even with indexes. Do not panic. Check the plan, check whether the columns used for joins and filters have indexes, check whether you are returning more rows or columns than needed, and ask a more experienced colleague or the database administrator. Do not add indexes to a live production database without agreement, since it can affect everyone using it.

Your task

1. Run EXPLAIN QUERY PLAN on SELECT * FROM order_items WHERE order_id = 5; and note the plan.
2. Create an index on order_items (order_id) and run it again. What changed in the words?
3. Rewrite WHERE SUBSTR(order_date, 1, 7) = '2024-07' so it can use an index on order_date.
4. Drop your index with DROP INDEX idx_name; and explain one downside of having too many indexes.

How to check it worked

• Before the index the plan says SCAN order_items; after it says SEARCH ... USING INDEX.
• The date rewrite uses a range: order_date >= '2024-07-01' AND order_date < '2024-08-01'.
• You can explain that indexes use space and slow writes.