Indexes

A table is a pile. An index is a contents page

Without an index, "the orders of customer 1" means reading every row and testing it: a scan. With one, the database jumps straight to the right place in a sorted structure and walks a few entries: a search. On twenty-four rows both are instant. On twenty-four million the first one is a page that never loads.

create index idx_orders_customer on orders (customer_id);

create unique index idx_doors_name on doors (name);     -- a constraint, enforced by the index
drop index idx_orders_customer;

Ask the database what it is doing

Every dialect has this: explain query plan in SQLite, explain analyze in PostgreSQL and MySQL. The words differ, the question does not — scan or search? If a query that should be instant is slow, the plan tells you why in one line, and guessing tells you nothing.

explain query plan
select * from orders where customer_id = 1;

-- before:  SCAN orders
-- after:   SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

A B-tree sorts, so it does more than equality

A composite index is sorted by the first column, then the second inside it — like a phone book by surname then first name. You can look up "Aliyev", and "Aliyev, R", but not "everyone called Rashad". The order of the columns is the design decision; equality first, then ranges.

where status = 'fitted'                        -- yes
where status = 'fitted' and placed > '2024-06-01'   -- yes
where status = 'fitted' order by placed        -- yes: the order comes free
where placed > '2024-06-01'                    -- no: placed is the second column
where status like 'fit%'                       -- yes: a prefix is a range
where status like '%ted'                       -- no: a leading % has no starting point

Covering: when the index is the answer

Every column the query needs is already in the index, so the table is never touched. That is the reason select * costs more than naming three columns, and the reason a sensible index can be several times faster than a merely adequate one.

create index idx_orders_status_placed on orders (status, placed);

select status, placed from orders where status = 'fitted' order by placed;
-- SEARCH orders USING COVERING INDEX idx_orders_status_placed (status=?)

What an index costs

Every insert, update and delete has to maintain every index on the table. Ten indexes make writes several times slower and the database several times larger, and an index nobody uses costs exactly as much as one everybody uses.

So: index the columns you filter, join and sort on. Index every foreign key — nothing does it for you outside MySQL, and deletes on the parent scan the child without it. Then look at what the application actually runs and delete the rest.

-- only the rows anyone queries
create index idx_open_orders on orders (placed) where status <> 'cancelled';

-- an index on what you actually compare
create index idx_doors_lower_name on doors (lower(name));

The ones people get wrong