Courses/SQL/Indexes & Performance

Lesson 11 • Expert Track

Indexes & Performance

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.

What You'll Learn

✓What an index really is (a sorted lookup structure)
✓Create indexes with CREATE INDEX and CREATE UNIQUE INDEX
✓Build composite (multi-column) indexes and the leftmost-prefix rule
✓When indexes speed up reads (WHERE / JOIN / ORDER BY)
✓The cost: extra storage and slower INSERT / UPDATE / DELETE
✓Read EXPLAIN / EXPLAIN QUERY PLAN to see if an index is used

Before you start: finish INSERT, UPDATE, DELETE so you understand how rows are written — that's exactly the work an index makes a little slower. The in-browser editor lets you write and edit SQL; to run it (and see real EXPLAIN plans), copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected plan or result so you can check yourself.

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

Result:

id	product_name	category	price	stock
1	Wireless Mouse	Electronics	24.99	120
2	Coffee Mug	Kitchen	9.5	300
3	Mechanical Keyboard	Electronics	79	45
4	Notebook	Stationery	3.25	500
5	Desk Lamp	Home	32	80
6	USB-C Cable	Electronics	12.99	200

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.

📖 Real-world analogy

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.

The slow path — no index

A WHERE filter with nothing to look up must scan every row.

Try it Yourself »

SQL

-- Imagine our products table held 5,000,000 rows.
-- With NO index, this filter has to read EVERY row to
-- find the matches — that's a "full table scan".
SELECT product_name, price
FROM products
WHERE category = 'Electronics';

-- It works, but on a big table it is slow because the
-- database has nowhere to "look up" Electronics quickly.

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

CREATE INDEX

Build a sorted lookup on the category column.

Try it Yourself »

SQL

-- An index is a sorted lookup structure for one (or more)
-- columns — like the index at the back of a book.
CREATE INDEX idx_category
ON products(category);

-- Now the same WHERE can jump straight to the matching
-- rows instead of scanning the whole table.
SELECT product_name, price
FROM products
WHERE category = 'Electronics';

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.

🎯 Your Turn: CREATE INDEX idx_category

Replace the ___ blanks with the index name and column.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: create an index named idx_category on the
-- products table's category column.

CREATE INDEX ___          -- 👉 the index name: idx_category
ON products(___);         -- 👉 the column to index: category

-- ✅ Expected: the statement runs with no error and the
--    index now exists. Re-running it errors with
--    "index idx_category already exists" — proof it worked.

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.

Pro tip: a PRIMARY KEY already creates a unique index for you behind the scenes. You only add your own unique index for other columns that must stay distinct.

CREATE UNIQUE INDEX

Speed up look-ups and block duplicate product names.

Try it Yourself »

SQL

-- A UNIQUE index does two jobs at once:
--   1) speeds up look-ups on the column, AND
--   2) refuses to store a duplicate value.
CREATE UNIQUE INDEX idx_product_name
ON products(product_name);

-- This now FAILS because "Coffee Mug" already exists:
INSERT INTO products (id, product_name, category, price, stock)
VALUES (7, 'Coffee Mug', 'Kitchen', 9.50, 50);
-- ❌ Error: UNIQUE constraint failed: products.product_name

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.

Composite index

One index covering category, then price.

Try it Yourself »

SQL

-- A composite (multi-column) index covers several columns,
-- in order. Column order matters: it is sorted by the FIRST
-- column, then the second, like a phone book (surname, then
-- first name).
CREATE INDEX idx_category_price
ON products(category, price);

-- This index helps a query that filters on category, OR on
-- category AND price together. It is the "leftmost prefix"
-- rule — the query must use the first column to benefit.
SELECT product_name
FROM products
WHERE category = 'Electroni
...

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

⚠️ The trade-off — indexes are not free

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.

EXPLAIN — before the index

Read the plan: a SCAN reads every row.

Try it Yourself »

SQL

-- EXPLAIN (or EXPLAIN QUERY PLAN in SQLite) does NOT run the
-- query — it shows the STRATEGY the database chose.
EXPLAIN QUERY PLAN
SELECT product_name, price
FROM products
WHERE category = 'Electronics';

-- BEFORE any index, the plan reads roughly like this:
--   SCAN products
-- "SCAN" = read every row. That is the slow path.

Expected plan — before index:

QUERY PLAN
--SCAN products

EXPLAIN — after the index

The plan switches to a fast index search.

Try it Yourself »

SQL

-- Add the index, then ask for the plan again:
CREATE INDEX idx_category ON products(category);

EXPLAIN QUERY PLAN
SELECT product_name, price
FROM products
WHERE category = 'Electronics';

-- AFTER the index, the plan changes to:
--   SEARCH products USING INDEX idx_category (category=?)
-- "SEARCH ... USING INDEX" = it jumped straight to the rows.

Expected plan — after index:

QUERY PLAN
--SEARCH products USING INDEX idx_category (category=?)

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.

🎯 Your Turn: predict the plan

Decide which query keeps the indexed column clean.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — you do NOT need to change any code here.
-- You created this index earlier:
--     CREATE INDEX idx_category ON products(category);
--
-- TWO queries are below. ONE can use idx_category, ONE cannot.
-- Decide which is which, then run EXPLAIN QUERY PLAN to check.

-- Query A:
SELECT * FROM products WHERE category = 'Kitchen';

-- Query B:
SELECT * FROM products WHERE LOWER(category) = 'kitchen';

-- 👉 Which one keeps the column "clean" (no function on it)?
-- ✅ Expected: A uses
...

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
CREATE INDEX idx ON t(col)	Add an index on one column
CREATE UNIQUE INDEX idx ON t(col)	Index + block duplicate values
CREATE INDEX idx ON t(a, b)	Composite index (column order matters)
DROP INDEX idx	Remove an index
EXPLAIN QUERY PLAN SELECT …	Show the strategy (SCAN vs SEARCH)
EXPLAIN ANALYZE SELECT …	Run it and show real timings

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.

🎯 Mini-Challenge

Pick and create one index that speeds up both WHERE and ORDER BY.

Try it Yourself »

SQL

-- 🎯 MINI-CHALLENGE — choose and create the RIGHT index.
-- The slow query you must speed up is:
--
--     SELECT product_name, price
--     FROM products
--     WHERE category = 'Electronics'
--     ORDER BY price;
--
-- 1. Decide which column(s) the database needs to look up
--    and sort by (hint: it filters category, then sorts price).
-- 2. Write ONE CREATE INDEX statement for that/those column(s).
-- 3. In a comment, say WHY your index helps both the WHERE and
--    the ORDER BY.
--
-- ✅
...

🎉 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

Indexes & Performance

What You'll Learn

Our Sample Table: products

1. What Is an Index?

The slow path — no index

2. Creating an Index with CREATE INDEX

CREATE INDEX

Your Turn: create the index

🎯 Your Turn: CREATE INDEX idx_category

3. UNIQUE Indexes

CREATE UNIQUE INDEX

4. Composite (Multi-Column) Indexes

Composite index

5. When Indexes Help — and What They Cost

6. Proving It with EXPLAIN

EXPLAIN — before the index

EXPLAIN — after the index

Your Turn: which query uses the index?

🎯 Your Turn: predict the plan

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Speed Up a Slow Query

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `products`