Home / SQL / Reference / Tables and design

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

idparentnotuseddetail
300SEARCH 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.