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.

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.

What You'll Learn

Our Sample Table: products

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".

1. What Is an Index?

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.

2. Creating an Index with CREATE INDEX

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".

Your Turn: create the index

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.

3. UNIQUE Indexes

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.

4. Composite (Multi-Column) Indexes

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.

5. When Indexes Help — and What They Cost

Indexes pay off whenever the database has to find or order rows. The biggest wins are columns used in:

Index helps with…

Why

Example

WHERE

Jump to matching rows

WHERE category = 'Home'

JOIN

Match rows across tables fast

ON o.product_id = p.id

ORDER BY

Rows are already sorted

ORDER BY price

GROUP BY

Grouping is faster on sorted data

GROUP BY category

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".

6. Proving It with EXPLAIN

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.

Your Turn: which query uses the index?

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.

Common Errors (and the fix)

• Indexing everything: adding an index to every column feels safe but bloats storage and slows every write. Index the columns you actually filter, join, or sort on — and measure with EXPLAIN before adding more.
• Index "ignored" — a function on the column: WHERE LOWER(category) = 'home' can't use an index on category , because the index stores the raw values, not the lowercased ones. Keep the column bare: WHERE category = 'Home' (or index the expression).
• Index "ignored" — leading wildcard LIKE: WHERE product_name LIKE '%mouse' forces a full scan, since the index is sorted by the start of the value. A trailing wildcard like 'Wireless%' can use the index.
• Forgetting indexes slow writes: after a bulk INSERT got slow, the cause is often the five indexes each row must update. Drop indexes you don't query, especially on write-heavy tables.
• "index idx_category already exists": you ran CREATE INDEX twice. Use CREATE INDEX IF NOT EXISTS … , or DROP INDEX idx_category; first.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

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.

Mini-Challenge: Speed Up a Slow Query

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.

🎉 Lesson Complete

• ✅ An index is a sorted lookup structure — a book's index for your data
• ✅ CREATE INDEX speeds up look-ups; CREATE UNIQUE INDEX also blocks duplicates
• ✅ Composite indexes cover several columns in order (leftmost-prefix rule)
• ✅ Indexes help WHERE / JOIN / ORDER BY but cost storage and slow writes
• ✅ EXPLAIN reveals whether the plan is a slow SCAN or a fast index SEARCH
• ✅ Next: Views & Stored Procedures — package reusable logic inside the database

Practice quiz

Continue this course

• Previous: INSERT, UPDATE, DELETE
• Next: Views & Stored Procedures