Part 2 of 5 · 1 min read
Creating an index
The basic CREATE INDEX statement, and what it speeds up.
Think of a book's index at the back, listing a term alongside the page numbers where it appears. Finding a term through the index is far faster than reading every page from the start.
In plain words: CREATE INDEX builds a separate structure the database can search quickly, mapping a column's values to the rows holding them. A query filtering or joining on an indexed column can use the index to find matching rows directly, instead of checking every row in the table one by one.
A worked example. Finding all orders for one customer, sped up by an index on orders.customer_id.
The key lines.
CREATE INDEX idx_orders_customer ON orders (customer_id);
SELECT * FROM orders WHERE customer_id = 107;
Index
An index is a separate structure mapping a column's values to the rows that hold them, letting the database find matching rows without checking every row of the table.
Example: idx_orders_customer lets WHERE customer_id = 107 jump straight to matching rows, instead of scanning the entire orders table.