SQL Indexes

What is an index?

An index is a sorted lookup structure (usually a B-tree) that lets the database find rows without scanning the whole table — like the index at the back of a book.

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;

id | parent | notused | detail
2 | 0 | 0 | SCAN orders

id | parent | notused | detail
3 | 0 | 0 | SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

EXPLAIN QUERY PLAN (SQLite) or EXPLAIN (PostgreSQL, MySQL) shows whether a query uses an index (SEARCH ... USING INDEX) or scans the table (SCAN).

When to add indexes

• Columns used in WHERE, JOIN ... ON and ORDER BY on large tables.
• Foreign key columns (orders.customer_id).
• Composite indexes (a, b) help queries filtering on a, or on a and b — but not on b alone.

Indexes aren't free: they use space and slow down INSERT/UPDATE/DELETE. A UNIQUE index also enforces uniqueness.