Indexes

What it is

Indexes accelerate selected reads at a write/storage cost.

When to use it

Index frequent filters and joins using query-plan evidence.

Syntax

CREATE INDEX orders_user_status ON orders (user_id, status);

Example

CREATE INDEX orders_user_status_id ON orders (user_id, status, id);
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE user_id = 7 AND status = 'pending'
ORDER BY id LIMIT 20;

Common mistakes

Column order matters; avoid indexing every column; foreign keys are not always automatically indexed.

Related topics