Indexes & Performance

Reviewed & published by Brayan K

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

• 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

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.

-- The table this lesson uses. Run this block first;
-- every later block queries it.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        NUMERIC,
    stock        INTEGER
);
INSERT INTO products VALUES
    (1, 'Wireless Mouse', 'Electronics', 25,  40),
    (2, 'Laptop Stand',   'Electronics', 45,  15),
    (3, 'USB-C Hub',      'Electronics', 60,  30),
    (4, 'Chef Knife',     'Kitchen',     35,  12),
    (5, 'Coffee Mug',     'Kitchen',     9.5, 80),
    (6, 'Kettle',         'Kitchen',     55,  20),
    (7, 'Plant Pot',      'Garden',      12,  60);

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

-- ✅ Expected result:
-- product_name | price
-- Wireless Mouse | 25
-- Laptop Stand | 45
-- USB-C Hub | 60

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

-- Uses the products table from the first example.
-- 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';

-- ✅ Expected result:
-- product_name | price
-- Wireless Mouse | 25
-- Laptop Stand | 45
-- USB-C Hub | 60

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 — fill in the blanks, then press "Try it Yourself"
-- Uses the products table from the first example.
-- Goal: index the price column, so range queries on price are fast.
-- (idx_category already exists from the earlier blocks, so this one
--  needs its own name.)

CREATE INDEX ___          -- 👉 replace ___ with idx_price
ON products(___);         -- 👉 replace ___ with price

-- Ask the database to prove it worked:
SELECT name FROM pragma_index_list('products') WHERE name = 'idx_price';

-- Run the whole block a second time and it stops with
-- "index idx_price already exists" — which is also proof it worked.

-- ✅ Expected result:
-- name
-- idx_price

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.

-- Uses the products table from the first example.
-- 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);

-- The insert below FAILS, because "Coffee Mug" is already in the
-- table. It is commented out so the rest of this block still runs —
-- uncomment it and everything after it stops with the real error:
--
--   INSERT INTO products (id, product_name, category, price, stock)
--   VALUES (8, 'Coffee Mug', 'Kitchen', 9.50, 50);
--   ❌ UNIQUE constraint failed: products.product_name

-- The index is real, and the database will list it for you:
SELECT name, "unique"
FROM pragma_index_list('products')
WHERE name = 'idx_product_name';

-- ✅ Expected result:
-- name | unique
-- idx_product_name | 1

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.

-- Uses the products table from the first example.
-- 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 = 'Electronics'
  AND price < 50;

-- ✅ Expected result:
-- product_name
-- Wireless Mouse
-- Laptop Stand

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:

⚠️ 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.

-- Uses the products table from the first example.
-- You already built idx_category above, so drop it first to see
-- what the "before" picture actually looks like:
DROP INDEX IF EXISTS idx_category;
DROP INDEX IF EXISTS idx_category_price;

-- 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';

-- With no index, the detail column reads:
--   SCAN products
-- "SCAN" = read every row. That is the slow path.
--
-- Read the LAST column, "detail" — it is the part SQLite documents
-- and the only part this lesson is about. The third column is
-- literally named "notused": it is internal scratch space and
-- different SQLite builds put different numbers in it, so this
-- block claims no exact row.

Expected plan — before index:

-- Uses the products table from the first example.
-- Put the index back, 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 detail column changes to:
--   SEARCH products USING INDEX idx_category (category=?)
-- "SEARCH ... USING INDEX" = it jumped straight to the rows.
-- (Same note as above: ignore the "notused" column.)

Expected plan — after index:

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.

-- Uses the products table from the first example.
-- 🎯 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 — run the plan for this one:
EXPLAIN QUERY PLAN SELECT * FROM products WHERE category = 'Kitchen';

-- Query B — then swap the line above for this one and look again:
--   EXPLAIN QUERY PLAN SELECT * FROM products WHERE LOWER(category) = 'kitchen';

-- 👉 Which one keeps the column "clean" (no function on it)?
-- A's detail says SEARCH ... USING INDEX idx_category.
-- B wraps category in LOWER(), so the index is useless to it and
-- the detail says SCAN products. Put a function around a column and
-- you throw its index away — that is the whole lesson in one line.

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.

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 — 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.
--
-- ✅ Expected: CREATE INDEX idx_cat_price ON products(category, price);
--    A composite (category, price) index filters by category AND
--    returns those rows already sorted by price — no extra sort.

-- your index here

🎉 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 — Create reusable views and stored procedures for complex query logic
• Quick reference: SQL cheat sheet › Tables & Schema
• From the blog: Database Indexing Strategies · SQL Database Design Best Practices