Indexes
By the end of this lesson you'll be able to make a slow query fast by adding the right INDEX — and prove it worked by reading the database's own execution plan. You'll also learn the cost of indexes, so you know when not to add one.
Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.
Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
Every example in this lesson uses this products table. It only has 6 rows so it runs instantly — but as you read, imagine it holding millions of rows . That's when indexes go from "nice" to "essential".
An index is a separate, sorted lookup structure the database keeps for one or more columns. Instead of reading every row to find what you want, the database consults the index and jumps straight to the matching rows.
Think of a 500-page textbook. To find every mention of "PostgreSQL", you could read all 500 pages start to finish — that's a full table scan . Or you flip to the alphabetical index at the back: "PostgreSQL → pages 247, 312" and jump there. The book's pages never move; the index is just an extra, sorted list of where things are . A database index is exactly that.
The key idea: an index doesn't change your data or your results — it only changes how fast the database can find rows. Without one, the only option for WHERE category = 'Electronics' is to check all rows.
You build an index with CREATE INDEX . You give it a name (so you can drop it later), the table , and the column(s) to sort. A common convention is to prefix the name with idx_ .
Once the index exists, the database can use it automatically — you don't change your SELECT at all. It quietly switches from "scan everything" to "look it up".
Fill in the two blanks to create an index named idx_category on the products(category) column. The expected outcome is in the comments so you can check yourself.
A CREATE UNIQUE INDEX does everything a normal index does and enforces a rule: no two rows may share that value. It's how you guarantee, say, that every email or username is one-of-a-kind — and the look-up stays fast as a bonus.
A composite index covers several columns at once, in the order you list them. It's sorted by the first column, then by the second within each group — just like a phone book is sorted by surname, then first name.
Leftmost-prefix rule: an index on (category, price) helps queries that filter on category alone, or on category AND price — but not a query that filters on price alone. You can't use the phone book to find everyone named "Sam" without a surname.
Indexes pay off whenever the database has to find or order rows. The biggest wins are columns used in:
Every index takes up extra storage , and it must be kept in sync. So every INSERT , UPDATE , and DELETE now does more work — it updates the table and every index on the affected columns. On a write-heavy table, too many indexes can make it slower overall. Index the columns you read and filter often; don't index everything "just in case".
How do you know an index is actually being used? You ask the database to show its plan. EXPLAIN (called EXPLAIN QUERY PLAN in SQLite) shows the strategy the database picked — without running the query. EXPLAIN ANALYZE goes further and actually runs it with real timings.
You're looking for one word. A plan that says SCAN (or Seq Scan in PostgreSQL) reads every row — the slow path. A plan that says SEARCH … USING INDEX (or Index Scan ) means your index is working.
No blanks to fill this time — read the two queries and predict which one can use idx_category . The hint and answer are in the comments; run EXPLAIN QUERY PLAN on each in a playground to confirm.
Q: Do I have to change my SELECT to "use" the index?
No. Once the index exists, the database's query planner decides to use it automatically when it helps. Your SELECT stays exactly the same — only the speed changes.
Q: Why would the database ignore an index I created?
Usually because the column isn't kept "clean" (a function or type conversion wraps it, or a LIKE starts with % ), or because the table is so small a scan is actually faster. On our 6-row table the planner may scan anyway — the rules matter at scale.
Q: Should I just index every column to be safe?
No. Each index costs storage and makes every INSERT / UPDATE / DELETE slower. Index the columns you frequently filter, join, or sort on, then verify with EXPLAIN .
Q: Does the column order in a composite index matter?
Yes — a lot. An index on (category, price) helps queries that filter on category (the leftmost column) but not ones that filter on price alone. Put the column you filter on most first.
Put it all together — a brief, a blank canvas, and the expected answer in the comments. Choose the right index for the described slow query, then explain why it helps.
Practice quiz
What is a database index, essentially?
- A copy of the whole table
- A faster data type
- A separate sorted lookup structure for one or more columns
- A backup of the table
Answer: A separate sorted lookup structure for one or more columns. An index is a sorted lookup structure — like the index at the back of a book — that helps find rows fast.
Without any index, how does WHERE category = 'Electronics' find rows?
- A full table scan — it reads every row
- It jumps straight to the rows
- It returns an error
- It uses the primary key automatically
Answer: A full table scan — it reads every row. With nothing to look up, the database must scan every row — slow on a big table.
What does CREATE UNIQUE INDEX do beyond a normal index?
- It indexes two columns
- It deletes duplicate rows
- It runs faster than a normal index
- It also blocks duplicate values in the column
Answer: It also blocks duplicate values in the column. A UNIQUE index speeds up lookups AND refuses to store duplicate values.
For a composite index on (category, price), which query can use it (leftmost-prefix rule)?
- A filter on price alone
- A filter on category alone, or category AND price
- Any query, regardless of columns
- Only queries with no WHERE
Answer: A filter on category alone, or category AND price. The leftmost-prefix rule: the query must use the first column (category) to benefit.
Which clauses can an index speed up?
- WHERE, JOIN, ORDER BY, GROUP BY
- Only WHERE
- Only ORDER BY
- INSERT and UPDATE
Answer: WHERE, JOIN, ORDER BY, GROUP BY. Indexes help finding and ordering rows — WHERE, JOIN, ORDER BY, and GROUP BY.
What is the main cost of adding indexes?
- Slower SELECT queries
- Loss of data
- Extra storage and slower INSERT/UPDATE/DELETE
- Tables can hold fewer rows
Answer: Extra storage and slower INSERT/UPDATE/DELETE. Each index uses storage and must be kept in sync, so every write does more work.
Why might WHERE LOWER(category) = 'home' ignore an index on category?
- LOWER is invalid SQL
- A function on the column means the raw indexed values can't be used
- Indexes never work with WHERE
- The table is too large
Answer: A function on the column means the raw indexed values can't be used. The index stores raw values; wrapping the column in a function forces a scan. Keep the column bare.
Which LIKE pattern can still use an index?
- A leading wildcard like '%mouse'
- Both equally
- Neither
- A trailing wildcard like 'Wireless%'
Answer: A trailing wildcard like 'Wireless%'. A leading wildcard forces a full scan; a trailing wildcard can use the sorted index.
What does EXPLAIN (or EXPLAIN QUERY PLAN) show?
- The query results
- The strategy the database chose, without running the query
- How many rows exist
- The table's schema
Answer: The strategy the database chose, without running the query. EXPLAIN reveals the plan (SCAN vs SEARCH USING INDEX) without executing the query.
In a SQLite plan, which text means the index is being used?
- SCAN products
- FULL TABLE
- SEARCH ... USING INDEX
- NO INDEX
Answer: SEARCH ... USING INDEX. SEARCH ... USING INDEX is the fast index path; SCAN means every row is read.