MySQL Indexing Techniques Every Developer Should Know

I once inherited a project where the orders page took 8 seconds to load. The database had 2 million rows. After adding two indexes, it loaded in under 100 milliseconds. Same data, same query, just indexes.

Understanding indexes is one of the highest-value skills a developer can have.

What is an index, really?

Think of a book. Without an index at the back, finding every mention of "Laravel" means reading every single page. With an index, you look up "Laravel" and go straight to the right pages.

A database index works the same way. Without it, MySQL reads every row in the table to find what you want. With it, MySQL jumps directly to the matching rows.

The most important index: your primary key

Every table should have a primary key. MySQL automatically creates an index on it. This is why User::find(5) is always fast — it uses the primary key index.

Adding indexes on columns you filter by

-- You frequently run: WHERE status = 'pending'
CREATE INDEX idx_status ON orders(status);

-- You frequently run: WHERE email = '...'
CREATE INDEX idx_email ON users(email);

In Laravel migrations:

$table->index('status');
$table->unique('email'); // unique also creates an index

Composite indexes: the order matters

A composite index covers multiple columns. The order you define them matters a lot.

CREATE INDEX idx_status_created ON orders(status, created_at);

This index helps queries that filter by status, or by status AND created_at. It does NOT help queries that only filter by created_at. MySQL can only use the index starting from the leftmost column.

Think of it like a phone book sorted by last name, then first name. You can find everyone named "Smith" easily. You can find "John Smith" easily. But you cannot find all "Johns" without reading the whole book.

Use EXPLAIN to see what MySQL is doing

This is the most important tool for index debugging:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01';

Look at the type column in the output:

• ALL — full table scan, no index used. This is bad on large tables.
• ref or range — using an index. Good.
• const — using a unique index to find one row. Best.

If you see ALL on a large table, you need an index.

Indexes have a cost

Every index makes reads faster but writes slower. When you insert or update a row, MySQL must update all the indexes on that table too.

Do not add indexes on every column. Add them on columns you actually query. A table with 20 indexes on a write-heavy workload will be slower than one with 5 well-chosen indexes.

The covering index trick

If your query only needs columns that are all in the index, MySQL can answer the query from the index alone without touching the actual table rows. This is called a covering index and it is very fast.

-- This query only needs user_id, status, and total
SELECT status, total FROM orders WHERE user_id = 5;

-- A covering index for this query:
CREATE INDEX idx_covering ON orders(user_id, status, total);

Start by running EXPLAIN on your slowest queries. Add indexes where you see full table scans. Measure the improvement. Repeat.