CSE Roadmap

Core tier · about 3 hours · 5 parts

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.