SQL Create Index
An index gives the database another way to locate rows. It can reduce work for a selective filter or join, but it also takes space and adds work to inserts, updates, and deletes.
Understand the idea
This index begins with customer_id and then ordered_at. It can help queries that select one customer and request date order. Index column order matters: the same index may be less useful for a query filtering only on a later column. A tiny dataset may still be scanned because a scan is cheap; test with representative data.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at);
EXPLAIN QUERY PLAN
SELECT order_id, ordered_at FROM orders
WHERE customer_id = 1
ORDER BY ordered_at;
Read the result
The output is a SQLite query plan describing how the query will be read. It should show a search using the new index, though exact plan text depends on runtime version.
Try one change
Compare a filter on customer_id with one on status. Explain which query matches the leading index column.