AI Hints
Home›SQL Tutorial›Rules, Safety, and Speed
SQL · Lesson 77 of 126

Indexes

Making queries fast.

⏱ 2 min read▶ Video at 1:48:09🎓 Free
Watch this lesson in the SQL courseWatch on YouTube ↗Full SQL course

Helen's shop has thousands of orders. A big online shop has millions. To find one customer's orders, the database might read every single row. That's slow. An index is like the index at the back of a book. It tells the database exactly where to look.

Indexes
From the video: Indexes
  • Millions of rows
  • Reading every row is slow
  • An index shows where to look

Without an index

Explain query plan shows how the database will run a query, without running it. Run it. Scan orders means it reads the whole table, row by row.

query.sqlsql
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;
Output
QUERY PLAN
`--SCAN orders

With an index

Now create an index on the customer ID column. Then ask for the plan again. Run it. Now it says search, using the index. Instead of reading every row, it jumps straight to customer forty two.

query.sqlsql
CREATE INDEX idx_orders_customer
ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;
Output
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

When to add an index

Indexes make reading faster, but they make inserts and updates a little slower, and they take up space. So add them to columns you often search or join on. Foreign keys, like customer ID, are the classic example.

FasterWHERE and JOIN
SlowerINSERT and UPDATE
Indexcolumns you search often
Classicforeign keys

Summary

An index helps the database find rows fast. Explain query plan shows whether it's being used. Next, how to design good tables from the start.

Key takeaways
  • CREATE INDEX ... ON t (col)
  • SCAN = slow, SEARCH = fast
  • Index what you search
Next up: Designing Good Tables — normalization made simple