SQL Indexes

Speed up searches, joins and sorting with indexes, and check how a query runs with EXPLAIN.

Lesson 21 of 102Advanced17 min read

With six products, every query is instant. With six million, a query that searches for one product name could take seconds, because without help the database has to look at every row to find the matches. That's called a full table scan, and it's the most common reason a website gets slower as its data grows.

An index fixes this. It's a separate, sorted structure that the database keeps alongside a table, pointing from values to the rows that contain them, much like the index at the back of a book points from a word to its page numbers. With an index on name, finding one product means jumping straight to the right place instead of reading the whole table.

In this lesson you'll learn how indexes work, create single-column, unique and multi-column indexes, choose which columns deserve one, check whether a query uses an index with EXPLAIN, and understand the price you pay for every index you add.

How an index works

Most databases store indexes as a B-tree: a balanced, sorted tree that can find any value in a handful of steps, even among millions of rows. Because it's sorted, the same index also helps with:

  • equality searches: WHERE email = 'ana@example.com'
  • ranges: WHERE order_date BETWEEN '2024-07-01' AND '2024-07-31'
  • sorting: ORDER BY order_date
  • joins: JOIN orders o ON o.customer_id = c.id

Primary keys and UNIQUE constraints get an index automatically. Other columns, including foreign keys in SQLite and PostgreSQL, don't, so you add those indexes yourself.

Creating indexes

SQL
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);
DROP INDEX idx_orders_customer;
  • Name indexes so you can recognise them later. A common pattern is idx_table_column.
  • A unique index also acts as a constraint: duplicate values are rejected.
  • Creating an index on a big table takes time and can lock it briefly, so on busy live databases it's done carefully, often outside peak hours.
  • In MySQL, DROP INDEX also needs the table: DROP INDEX idx_name ON orders;.

Composite indexes

An index can cover several columns, in a chosen order:

SQL
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

It's sorted by customer_id first, then by order_date within each customer, like a phone book sorted by surname, then first name. It speeds up queries that filter on customer_id, and on customer_id and order_date together. It does not help a query that filters only on order_date, just as a phone book doesn't help you find everyone called "Sam". Put the column you filter on most often, usually with =, first.

Checking a query with EXPLAIN

EXPLAIN shows how the database plans to run a query, without running it. In SQLite use EXPLAIN QUERY PLAN:

SQL
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

Look for the words SCAN, which means every row is read, and SEARCH ... USING INDEX, which means an index is used. MySQL and PostgreSQL use plain EXPLAIN, and PostgreSQL's EXPLAIN ANALYZE actually runs the query and reports real timings. Checking the plan before and after adding an index shows whether the index really helps.

SQL Indexes Syntax

Index syntax

CREATE INDEX index_name ON table_name (column);
CREATE INDEX index_name ON table_name (col1, col2);
CREATE UNIQUE INDEX index_name ON table_name (column);
DROP INDEX index_name;

EXPLAIN QUERY PLAN SELECT ...;   -- SQLite
EXPLAIN SELECT ...;              -- MySQL, PostgreSQL

SQL Indexes Summary

  • Without an index, a search reads every row: a full table scan.
  • An index is a sorted structure (usually a B-tree) that finds rows quickly.
  • Index columns used in WHERE, JOIN and ORDER BY on large tables.
  • Composite indexes help queries that use their leading column(s).
  • Use EXPLAIN to check the plan; remember indexes slow down writes.

SQL Indexes Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Before and after an index

SQL
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;
Output
QUERY PLAN
`--SCAN orders

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
Line-by-line explanation
  1. Lines 1 to 2Without an index, SQLite has to scan every order.
  2. Line 4Index the foreign key column used for lookups and joins.
  3. Lines 6 to 7Now SQLite searches the index and reads only the matching rows.
Example 2

A unique index as a safety net

SQL
ALTER TABLE customers ADD COLUMN email VARCHAR(255);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);

UPDATE customers SET email = 'ana@example.com' WHERE id = 1;
UPDATE customers SET email = 'ana@example.com' WHERE id = 2;
Output
1 row updated.
Error: UNIQUE constraint failed: customers.email
Line-by-line explanation
  1. Line 2The unique index makes email searches fast and forbids duplicates.
  2. Line 5Ben can't be given the same email as Ana.
Example 3

Which queries can use a composite index

SQL
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE customer_id = 1 AND order_date >= '2024-07-02';

EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE order_date >= '2024-07-02';
Output
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_cust_date (customer_id=? AND order_date>?)

QUERY PLAN
`--SCAN orders
Line-by-line explanation
  1. Lines 3 to 4Both columns are used, in the index's order, so the index helps fully.
  2. Lines 6 to 7Filtering only on the second column can't use this index, so SQLite scans.

Common SQL Indexes Mistakes

Indexing everything

Don't
CREATE INDEX i1 ON products (name);
CREATE INDEX i2 ON products (category);
CREATE INDEX i3 ON products (price);
CREATE INDEX i4 ON products (stock);
-- ...on a small table that's written to constantly
Do
CREATE INDEX idx_products_category ON products (category);   -- the column you actually filter on

Wrapping an indexed column in a function

Don't
SELECT * FROM orders WHERE strftime('%Y', order_date) = '2024';
Do
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

The function hides the column from the index. A plain range on the column can use it.

Leading wildcards

Don't
SELECT * FROM products WHERE name LIKE '%lamp';    -- can't use an index on name
Do
SELECT * FROM products WHERE name LIKE 'Desk%';    -- prefix searches can use it

For searching words inside text, databases offer full-text search features instead.

SQL Indexes in Real Projects

Indexes in production

When a page on a busy site suddenly slows down, developers find the slow query in the database's logs, run EXPLAIN on it, and very often discover a missing index on a foreign key or a commonly filtered column. Adding one index can turn a five-second query into a five-millisecond one, which is why understanding indexes is one of the most valuable database skills.

SQL Indexes FAQ

Does a primary key create an index?

Yes. Every major database creates an index to enforce a primary key, and the same applies to UNIQUE constraints. You don't need to add separate indexes on those columns. Foreign key columns are a different matter and often need their own index.

Why isn't the database using my index?

Common reasons: the table is small, so reading it all is faster; the condition matches a large share of rows; the column is wrapped in a function or compared with a value of a different type; or a composite index doesn't start with the filtered column. EXPLAIN shows the plan and helps you test each idea.

How many indexes should a table have?

As many as your important queries need, and no more. Every index speeds up some reads but slows down every insert, update and delete and uses storage. Start with the primary key, foreign keys and columns used in frequent filters and sorts, then add more when EXPLAIN shows a real need.

Can an index speed up ORDER BY?

Yes, if the index order matches the sort. An index on (category, price) lets WHERE category = 'Kitchen' ORDER BY price read rows already in order, avoiding a separate sort step. This matters most for queries with LIMIT, such as "the 10 cheapest products in a category".

What is a clustered index?

An index that decides the physical order of the table's rows. MySQL's InnoDB stores each table in primary key order, and SQL Server lets you choose one clustered index per table. PostgreSQL stores rows separately from all its indexes, so every index there works like a non-clustered one.

SQL Indexes Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the statement that adds an index on city.

 idx_customers_city  customers (city);
Multiple choiceeasy

What does a full table scan mean?

Multiple choicemedium

Which columns automatically get an index?

Predict the outputmedium

An index exists on (category, price). Which query can't use it efficiently?

Multiple choicehard

What's the downside of adding many indexes to a table?

SQL Indexes Quiz

5 questions to check what you learned.

  1. What is the main purpose of an index?

    • To find rows faster
    • To store backups
    • To enforce data types
    • To join tables

  2. In SQLite's EXPLAIN QUERY PLAN output, which word shows an index is used?

    • SCAN
    • SEARCH ... USING INDEX
    • INDEXED
    • FAST

  3. What data structure do most database indexes use?

    • A linked list
    • A queue
    • A B-tree
    • A stack

  4. Why can WHERE UPPER(name) = 'PEN' miss an index on name?

    • The function changes the value, so the sorted index can't be used directly
    • UPPER is slower than LOWER
    • Indexes don't work with text
    • It returns NULL

  5. Which query benefits from an index on orders(order_date)?

    • SELECT COUNT(*) FROM customers
    • SELECT * FROM products WHERE price > 10
    • INSERT INTO orders VALUES (...)
    • SELECT * FROM orders WHERE order_date >= '2024-07-01' ORDER BY order_date

Related SQL topics

19ViewsSave a query as a named view, query it like a table, and use views to simplify reports and limit access.22TransactionsGroup changes into all-or-nothing transactions with BEGIN, COMMIT and ROLLBACK, and understand ACID.06FunctionsTransform values inside a query with string, number, date and NULL-handling functions.
Esc
↑↓ to navigateEnter to open