How database indexing works (with MySQL examples)

1. What is a database index
2. How indexes work under the hood
3. Creating an index in MySQL
4. Verify the index is actually used
5. What to index
6. Composite indexes and the leftmost-prefix rule
7. The cost of indexes
8. Common mistakes
9. Conclusion

When a query gets slow, the cause is almost always a missing index. Indexing is the highest-impact thing you can do for database performance, yet it stays a little mysterious. This guide explains what an index is, how it speeds up reads, and how to add and verify indexes in MySQL.

#What is a database index

An index is a separate, sorted data structure that lets the database find rows without scanning the whole table.

The classic analogy is the index at the back of a book. To find every mention of "InnoDB", you don't read all 400 pages, you flip to the index, jump to "I", and get the exact page numbers. A database index does the same thing for your rows.

Without an index, MySQL has to do a full table scan, reading every row to find the ones that match. On a few hundred rows that's instant. On a few million it's the difference between 2 milliseconds and 2 seconds.

#How indexes work under the hood

Most MySQL indexes (everything in InnoDB by default) are stored as a B-tree: a balanced tree that keeps values in sorted order and stays shallow even for huge tables.

Because the tree is sorted and balanced, MySQL finds any value in a handful of steps instead of a linear scan. A table with a million rows is only a few levels deep, so a lookup touches a handful of nodes rather than a million rows. That same sorted structure is also why an index can satisfy range conditions (>, <, BETWEEN) and ORDER BY without sorting afterwards.

The trade-off: the tree has to stay sorted, so every INSERT, UPDATE, and DELETE has to update every affected index too. More on that cost below.

#Creating an index in MySQL

Say you frequently look up users by email:

SELECT * FROM users WHERE email = 'jane@example.com';

If email isn't indexed, that's a full table scan. Add an index:

CREATE INDEX idx_users_email ON users (email);

Or while creating/altering the table:

ALTER TABLE users ADD INDEX idx_users_email (email);

If the column should be unique (like an email), use a unique index instead, which enforces uniqueness and speeds up lookups:

ALTER TABLE users ADD UNIQUE INDEX idx_users_email (email);

In a Laravel migration the equivalent is:

$table->string('email')->unique();   // unique index
$table->index('last_login_at');       // plain index

#Verify the index is actually used

Adding an index is only half the job, you need to confirm MySQL uses it. Put EXPLAIN in front of your query:

EXPLAIN SELECT * FROM users WHERE email = 'jane@example.com';

Look at two columns:

• type — ALL means a full table scan (bad). ref, eq_ref, or const means an index is being used (good).
• key — the index MySQL chose. NULL here means no index was used.

If key is NULL after you added an index, the query usually isn't written in an index-friendly way (see common mistakes below).

#What to index

Index the columns that appear in:

• WHERE filters — WHERE status = 'active'
• JOIN conditions — the foreign-key columns on both sides
• ORDER BY / GROUP BY — an index can return rows already sorted, skipping a separate sort step

Foreign keys are a common blind spot. A column like posts.user_id used in joins should almost always be indexed, otherwise every join does a scan.

#Composite indexes and the leftmost-prefix rule

When a query filters on several columns together, a composite index beats several single-column ones:

ALTER TABLE orders ADD INDEX idx_orders_user_status (user_id, status);

This index helps queries that filter on user_id, or on user_id and status together:

SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

The catch is the leftmost-prefix rule: MySQL can only use the index left-to-right. The index above helps user_id alone, and user_id + status, but not status alone, because status isn't the leftmost column. Order the columns by how you actually query them.

#The cost of indexes

Indexes are not free, so don't index every column:

• Slower writes — every INSERT/UPDATE/DELETE must update each index. Over-indexing a write-heavy table hurts.
• Disk and memory — indexes take space and compete for the InnoDB buffer pool.
• Maintenance — redundant indexes (e.g. an index on (a) when you already have (a, b)) waste resources for no gain.

A good rule of thumb: add indexes to support your real, slow queries, then remove ones that EXPLAIN never chooses.

#Common mistakes

• Wrapping the column in a function — WHERE DATE(created_at) = '2026-06-23' can't use an index on created_at. Rewrite as a range: WHERE created_at >= '2026-06-23' AND created_at < '2026-06-24'.
• Leading wildcards — WHERE name LIKE '%smith' can't use an index; LIKE 'smith%' can.
• Indexing low-cardinality columns — an index on a boolean or a status with two values rarely helps on its own.
• Hitting the key-length limit — very long VARCHAR indexes can fail on older MySQL. See MySQL 1071: specified key was too long.

#Conclusion

Indexing is the first place to look when a query is slow. Find the columns in your WHERE, JOIN, and ORDER BY clauses, add a focused index, and confirm with EXPLAIN that MySQL uses it, while keeping an eye on write cost so you don't over-index.

If you're still on an older MySQL release, upgrading also unlocks better indexing limits and a smarter optimizer, see upgrading MySQL 5.7 to 8.0 on Ubuntu.