Indexes — what & when
Overview
An index is a hidden data structure (usually a B-tree, which is a balanced tree structure that keeps data sorted) that allows the database to find rows quickly without scanning the entire table. They are automatically created for Primary and Unique keys.
As tables grow to millions of rows, scanning the entire table for a single row becomes painfully slow. Indexes turn a full-table scan into a rapid lookup.
Where used: foreign keys, frequently filtered columns (WHERE), columns used for sorting (ORDER BY)
Why learn this
- You can fix slow queries by identifying and adding missing indexes.
- You understand the trade-off between read performance (faster) and write performance (slower).
Query walkthrough
SELECT o.id, o.order_date FROM orders o WHERE o.customer_id = 1 ORDER BY o.order_date DESC;Focus: An index on customer_id dramatically speeds up the WHERE clause, and an index could also help the ORDER BY clause.
Common mistakes
- Indexing every column: Indexes speed up SELECTs but slow down INSERTs, UPDATEs, and DELETEs because the index must be updated too. They also consume disk space. Only index what is necessary.
Glossary
- structure
- The way data is organized and stored so it can be accessed efficiently, like a filing system for information.
- rapid
- Happening in a very short amount of time; extremely fast or quick.
- consume
- To use up resources, such as disk space or processing power.
Recall questions
- Why shouldn't you add an index to every column in a table?
Understanding checks
What rows does this return?
name: Farah
This query uses the primary key index on `id` to instantly find Farah's record without scanning the other 7 rows.
What rows does this return?
order_date: 2023-05-10
If `customer_id` is indexed, the database finds customer 4's orders quickly. The result is just one order date for Diana.
Practice tasks
Filter on Likely Indexed Column
Given this query, change it to filter for the employee with the exact `id` of 5, which utilizes the primary key index.
Challenge
Query by Foreign Key
Write a query that finds the `id` and `quantity` of all order items for `order_id = 6`. This is a classic query that benefits from an index on the foreign key.
Continue learning
Previous: Primary, foreign & unique keys