🎓 A1Learner
🔍

SQL Indexes

An index is a lookup structure that helps the engine find rows faster. It changes how fast a query runs, never which rows it returns.

A search without an index

This query finds the orders of customer 2. The engine scans the orders table to find them.

SELECT order_id, customer_id, quantity FROM orders WHERE customer_id = 2 ORDER BY order_id;

Build the index

This statement builds an index on the customer_id column of orders. It returns no rows.

CREATE INDEX idx_orders_customer ON orders(customer_id);

Example

CREATE INDEX idx_orders_customer ON orders(customer_id);
SELECT order_id, customer_id, quantity FROM orders WHERE customer_id = 2 ORDER BY order_id;

Key points

  • CREATE INDEX: the statement that builds an index
  • An index is built on one table and one of its columns.
  • Use an index on a column that queries often search by.
  • An index changes speed, never results.

In this example, CREATE INDEX builds idx_orders_customer on the customer_id column. The SELECT then runs the same search as before. It returns the same two orders, because an index changes speed, never results.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does an index change about a query?