Indexes: making queries fast

Why queries get slow

Without help, WHERE phone = '9810012345' makes the database read every row and test it. That is called a full table scan. With 500 rows nobody notices. With 50 lakh rows, every login or search reads 50 lakh rows, and the app crawls.

A database index is that sorted list. Most databases store it as a B-tree, a structure that stays sorted as you add rows, so finding one value among crores of rows takes only a handful of steps. Each entry points back to the full row.

CREATE INDEX idx_orders_customer ON orders(customer_id);

-- now this can jump straight to customer 2's orders
SELECT id, amount FROM orders WHERE customer_id = 2;

UNIQUE indexes

A UNIQUE index does two jobs: it speeds up lookups and it refuses duplicates. It is how you guarantee one account per email or phone, even when two signup requests arrive at the same moment, which a check in your JavaScript code cannot promise. A PRIMARY KEY or a UNIQUE column constraint creates one for you automatically.

CREATE UNIQUE INDEX idx_customers_phone ON customers(phone);

INSERT INTO customers (name, phone) VALUES ('Aarav', '9810012345');
INSERT INTO customers (name, phone) VALUES ('Someone', '9810012345');
-- Error: UNIQUE constraint failed: customers.phone

Composite indexes and column order

A composite index covers several columns, sorted by the first column, then by the second inside it, like a phone book sorted by surname and then first name. An index on (customer_id, order_date) helps queries that filter on customer_id, or on customer_id and order_date. It cannot help a query that filters only on order_date, just as a surname-first phone book cannot find everyone named "Priya".

Checking the plan

Ask the database how it will run a query. In SQLite write EXPLAIN QUERY PLAN before the query: SCAN means it reads the whole table, SEARCH ... USING INDEX means it jumps. In PostgreSQL use EXPLAIN (the plan) or EXPLAIN ANALYZE (actually runs it and shows real times); look for Seq Scan versus Index Scan.

sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;
-- before the index:
SCAN orders
-- after CREATE INDEX idx_orders_customer:
SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

The cost, and when not to index

• Every INSERT, UPDATE and DELETE must also update every index on the table, so too many indexes slow writes.
• Indexes take disk space and memory.
• Tiny tables (a few hundred rows) are fast to scan anyway.
• Columns with very few different values (status with 3 values, a true/false flag) rarely help alone.
• A function on the column, like WHERE LOWER(email) = ..., cannot use a plain index on email.