Indexes

Learn what indexes are, how to create them, and how they speed up lookups at the cost of slightly slower writes and extra storage.

Introduction

As a table grows to thousands or millions of rows, searching through it row by row becomes slow. MySQL solves this with indexes — extra data structures that let it find matching rows almost instantly instead of scanning the whole table.

This lesson explains what an index is, how to create one, and the tradeoffs involved in deciding when to add one.

What You Will Learn

What an index is and how it speeds up lookups.
How to create an index with CREATE INDEX.
How a primary key automatically creates an index.
The tradeoff between faster reads and slower writes.
When adding an index is worth it, and when it is not.

What is an Index?

An index is a data structure that speeds up lookups on one or more columns, similar to the index at the back of a book. Instead of reading every single page to find a topic, you check the index, jump straight to the right page, and skip everything else.

Without an index, MySQL performs a "full table scan" — checking every single row to find matches. With an index on the right column, MySQL can jump almost directly to matching rows instead.

Book Index Analogy

Searching an un-indexed table for "orders placed by customer 482" is like reading an entire book page by page looking for the word "482." An index is like flipping straight to the right page using the book's index at the back.

Creating an Index

An index is created with CREATE INDEX, naming the index and specifying the table and column(s) it should cover.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10, 2)
);

CREATE INDEX idx_customer_id
ON orders(customer_id);

Now, any query that filters or joins on customer_id can use this index to find matching rows quickly instead of scanning the entire "orders" table.

SELECT * FROM orders
WHERE customer_id = 482;

Result

+----+-------------+------------+--------+
| id | customer_id | order_date | total  |
+----+-------------+------------+--------+
| 17 | 482         | 2026-02-10 | 59.99  |
| 44 | 482         | 2026-03-02 | 120.00 |
+----+-------------+------------+--------+

Primary Keys and Indexes

You do not always need to create an index manually. Declaring a PRIMARY KEY on a column automatically creates an index on it, because MySQL needs to enforce uniqueness and look up rows by that key efficiently. UNIQUE constraints behave the same way.

Automatic Indexes

A PRIMARY KEY column is automatically indexed.
A UNIQUE column or constraint is automatically indexed.
Any other column you frequently search on needs a manually created index.

The Tradeoff

Indexes are not free. Every index MySQL maintains must be updated whenever a row is inserted, updated, or deleted, which adds a small amount of extra work to every write, plus extra disk space to store the index itself.

Aspect	Without Index	With Index
SELECT / WHERE / JOIN	Slow on large tables (full scan)	Fast (direct lookup)
INSERT / UPDATE / DELETE	Slightly faster (nothing to maintain)	Slightly slower (index must be updated too)
Storage	No extra space used	Extra disk space for the index structure

Not a Free Speedup

Adding an index to every column is not a shortcut to a faster database. Each extra index adds write overhead and storage cost, so indexes should be added deliberately, not everywhere.

When to Add an Index

Add an index when a column is frequently used to filter, join, or sort a large table.

Add an index when...

A column appears often in WHERE clauses, JOIN conditions, or ORDER BY on a large, frequently-queried table.

Skip an index when...

The table is small (a full scan is already fast), the column is rarely searched on, or the table is written to far more often than it is read.

Rule of Thumb

On a table with only a few hundred rows, a full table scan is already essentially instant — an index there adds cost with no meaningful benefit. Indexes earn their keep on large, read-heavy tables.

Viewing and Dropping Indexes

Existing indexes on a table can be listed with SHOW INDEX, and removed with DROP INDEX when they are no longer needed.

SHOW INDEX FROM orders;

DROP INDEX idx_customer_id ON orders;

Common Mistakes

Avoid These Mistakes

Adding an index to every column "just in case," which slows down writes without a real read benefit.
Forgetting that a primary key already provides an index — creating a redundant duplicate index on it.
Adding indexes to small tables where a full scan was already fast.
Not indexing columns that are actually used constantly in WHERE or JOIN clauses on large tables.

Best Practices

Index columns used often in WHERE, JOIN, and ORDER BY on large tables.
Avoid indexing every column by default — treat each index as a deliberate tradeoff.
Remember primary keys and UNIQUE columns are already indexed automatically.
Periodically review SHOW INDEX output and drop indexes that are no longer used.

Frequently Asked Questions

Does adding an index make every query faster?

No. It only speeds up queries that actually use the indexed column in a WHERE, JOIN, or ORDER BY clause — it has no effect on unrelated queries.

Can I index more than one column at once?

Yes, this is called a composite (or multi-column) index, created with CREATE INDEX idx_name ON table(col1, col2), and is useful when queries frequently filter on that same combination of columns.

Does an index slow down SELECT queries?

No, indexes only add overhead to writes (INSERT/UPDATE/DELETE). SELECT queries that use the indexed column benefit — they never get slower because of it.

How many indexes should a table have?

There is no fixed number — it depends on your actual query patterns. Add indexes for columns you genuinely search or join on often, and avoid adding indexes you cannot justify.

Key Takeaways

An index is a data structure that speeds up lookups on a column, much like a book's index.
CREATE INDEX index_name ON table(column) creates one manually.
A PRIMARY KEY (and UNIQUE constraints) automatically create an index.
Indexes speed up reads but slightly slow down writes and use extra storage.
Add indexes to columns frequently used in WHERE/JOIN/ORDER BY on large tables — skip them on small tables.

Summary

Indexes let MySQL find matching rows quickly instead of scanning an entire table, at the cost of slightly slower writes and extra storage. Used deliberately, on the right columns of the right tables, they are one of the most effective performance tools available.

In this lesson, you learned what an index is, how to create and drop one, and when adding one is actually worthwhile. Next, you will learn about stored procedures — reusable blocks of SQL logic saved inside the database itself.

Lesson 54 Completed

You understand what an index is and why it speeds up lookups.
You can create an index with CREATE INDEX.
You know the tradeoff indexes introduce for writes and storage.
You are ready to learn about stored procedures.

Next Lesson →