If your application feels slow, the database is the first place to look — and a missing index is the most common cause. An index can turn a query that scans a million rows into one that reads three or four. This guide explains indexing in plain language, with examples you can run on any MySQL 8 database.
What Is an Index, Really?
Think of a database table as a thick book with no table of contents. To find one topic, you flip through every page. An index is the contents page at the front: a separate, sorted structure that tells MySQL exactly where a row lives, so it can jump straight to it.
MySQL stores most indexes as a B-tree: a balanced tree that stays sorted and can be searched very quickly, even with tens of millions of rows.
See the Problem with EXPLAIN
Never guess — measure. Put EXPLAIN in front of any SELECT and MySQL shows its plan:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
If the type column says ALL, MySQL is scanning the whole table (a full table scan). The rows column shows roughly how many rows it expects to examine. On a small table that is fine. On a growing orders table, it is a time bomb.
Adding Your First Index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
Run the same EXPLAIN again. The type should now be ref, key should show your new index, and rows should drop dramatically — often from hundreds of thousands to just the handful of orders that customer actually has.
In Laravel, add indexes in your migrations so they are never forgotten:
Schema::table('orders', function (Blueprint $table) {
$table->index('customer_id');
});
Composite Indexes and the Leftmost Rule
One index can cover several columns:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
Column order matters. MySQL can use this index for queries filtering on customer_id alone, or on customer_id and status — but not efficiently for status alone. This is the leftmost prefix rule: an index is useful from its leftmost column onward. Put the column you filter by most selectively first.
When You Should NOT Add an Index
Indexes are not free:
- Every insert and update gets slower, because each index must be updated too.
- Indexes use disk and memory. Ten indexes on one table is usually a smell.
- Tiny tables do not need them — scanning 50 rows is already instant.
- Columns with very few distinct values (like a yes/no flag alone) rarely benefit.
Index for the queries you actually run, not for every column "just in case".
Five Indexing Mistakes to Avoid
- No index on foreign keys — joins and
WHERE customer_id = ...crawl without one. - Wrapping an indexed column in a function —
WHERE DATE(created_at) = '2026-01-01'cannot use the index. Compare ranges instead:created_at >= '2026-01-01' AND created_at < '2026-01-02'. - Duplicate indexes — an index on
(customer_id)is redundant if(customer_id, status)already exists. - Indexing but never checking — run
EXPLAINafter adding an index and confirm the plan changed. - Ignoring slow queries until production — enable the slow query log in development and fix problems early.
A Simple Checklist
- Every foreign key column: indexed.
- Columns in frequent
WHERE,ORDER BYandGROUP BYclauses: considered. - Composite indexes ordered by the leftmost rule.
- Every important query verified with
EXPLAIN. - No duplicate or unused indexes (check with
SHOW INDEX FROM table_name).
Slow database queries are exactly the kind of problem this blog collects — browse the errors and fixes library for step-by-step solutions, or read how these lessons show up in real projects in the REST API tutorial. More database guides live in the MySQL Databases category.