How Databases Use Indexes
Overview
When a query filters or sorts rows, the database can use an index (like a book's index) to find matching rows instantly instead of scanning every row in the table. For example, finding all 'Electronics' in products using an index avoids checking the furniture rows. However, if a query requests a large percentage of the table's rows, a full sequential scan is often faster than jumping back and forth using an index.
Without indexes, databases perform 'Sequential Scans' which become painfully slow as tables grow to millions of rows. Understanding how the database uses indexes helps you write queries that scale.
Where used: optimizing WHERE clauses, speeding up JOINs
Why learn this
- Identify why a specific query is slow.
- Write WHERE clauses that actually use the indexes you've built.
Query walkthrough
SELECT name, price FROM products WHERE category = 'Furniture' ORDER BY price DESC;Focus: The WHERE clause utilizes the index on category to quickly locate Desk and Chair, rather than scanning the stationery and electronics items.
Common mistakes
- Function calls on indexed columns: Applying a function like LOWER(category) = 'electronics' prevents the database from using an index on `category`. Fix: Create an expression index, or match exactly.
Glossary
- scan
- When the database system reads through rows of data sequentially, one by one, to find the information it needs.
- scale
- The ability of a system, database, or query to handle growing amounts of data or users without losing performance.
- sequential
- Following in a logical order or sequence; checking items one after another rather than jumping directly to the answer.
Recall questions
- Why might a database choose to scan a whole table even if an index exists?
Understanding checks
What rows does this return?
Keyboard Mouse Monitor
It filters the products table down to the 3 electronics items. An index on `category` would make finding these 3 items fast.
What rows does this return?
Monitor Desk
Filters for items over 100. An index on `price` could help locate Monitor (199.99) and Desk (149.99).
Practice tasks
Filter with Exact Match
Given this query, change it to filter for the 'US' country. Assume there is an index on country that you want to use.
Challenge
Indexed Join Field
Write a query that finds the status of orders placed by customer 1. Filtering on customer_id is typically fast because foreign keys often have indexes.
Continue learning
Previous: Indexes — what & when
Next: Reading EXPLAIN ANALYZE