SQL CREATE INDEX
Make look-ups on some columns fast.
Syntax
CREATE INDEX name ON table (col1, col2);
An index lets the database jump to matching rows instead of scanning the whole table. Check with EXPLAIN QUERY PLAN.
Example
Query (on the shop database used in the SQL course)
CREATE INDEX idx_orders_customer ON orders (customer_id); EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 7;
Result
| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 0 | SEARCH orders USING INDEX idx_orders_customer (customer_id=?) |
1 row
Related
Learn it step by step in the free lesson Designing a Database, with exercises that check your queries.