The indexes you're missing (and the one that's hurting you)

A client sent me a query that took 4.2 seconds. One WHERE, one ORDER BY, a table with three million rows. I added a single index and it dropped to 11 milliseconds. They asked if I'd rewritten the query. I hadn't touched it. I just told the database how to find the rows instead of making it read all of them.

Indexing is the highest-leverage thing you can do to a slow Laravel app, and most of what's written about it stops at "add an index to columns you search." That's true but it's the beginner half. Here's the rest.

Composite indexes: order is everything

You filter orders by status and then sort by created_at. Two separate single-column indexes won't help much — the database can usually only use one of them for a query like this. What you want is one composite index across both:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['status', 'created_at']);
});

The catch nobody mentions: the column order inside the index matters, and it has to match how you query. A composite index on (status, created_at) works like a phone book sorted by last name, then first name. It's brilliant for "find everyone named Ghithan, sorted by first name." It's useless for "find everyone whose first name is Majd" — wrong leading column.

So the rule is: the columns you filter on with = go first, and the column you sort or range-scan on goes last. WHERE status = ? ORDER BY created_at wants (status, created_at), in exactly that order.

The middle bar is the trap. People add an index per column, see it get "a bit faster," and assume they're done. The composite is two orders of magnitude better because it satisfies the filter and the sort from one structure.

Index your foreign keys

Laravel creates a foreign key constraint when you write foreignId('user_id')->constrained(). What a lot of people don't realise is that in MySQL an FK constraint usually gets an index automatically — but the moment you define the FK manually, or you're on a setup where it doesn't, every Order::where('user_id', $id) and every with('orders') eager-load becomes a full table scan.

Any column you join on or filter a relationship by needs an index. If you have a comments table with post_id and you ever load $post->comments, post_id must be indexed. This is the single most common missing index I find, because the relationship "just works" in development — it's only slow, never broken.

Covering indexes: skip the table entirely

Here's the trick that feels like cheating. If an index contains every column a query needs — both the ones it filters on and the ones it returns — the database can answer the query from the index alone and never touch the table. That's a covering index.

// Query: select id, status from orders where user_id = ?
$table->index(['user_id', 'status', 'id']);

Now SELECT id, status FROM orders WHERE user_id = ? reads only the index. No jumping back to the table row for each match. On a hot endpoint that returns a small set of columns, this is often the difference between "fast" and "instant." You don't need it everywhere — reach for it on your top three slowest read queries.

When an index hurts

This is the part the tutorials skip, and it's why "just index everything" is wrong advice.

Every index is a copy of some columns that the database has to keep sorted. That copy isn't free — it's maintained on every INSERT, UPDATE, and DELETE. On a write-heavy table, each extra index is a tax on every write.

orders, products, users
read 1000s of times per write
index every filter + sort column
covering indexes on hot reads

events, logs, telemetry, audit_trail
written constantly, read rarely
every index slows every insert
keep only what a real query needs

I once watched an events table's insert throughput drop by a third because someone had added six indexes "to be safe." Five of them were never used by any query. We dropped them, writes recovered, and nothing got slower — because nothing was reading those columns anyway.

Read EXPLAIN before you guess

Stop guessing which index you need. Ask the database:

EXPLAIN SELECT id, status FROM orders WHERE user_id = 42 ORDER BY created_at;

Two things to look at. type: if it says ALL, that's a full table scan — the thing you're trying to kill. You want ref or range. And rows: the estimated number of rows examined. If it's reading two million to return twenty, you're missing an index. After you add one, run EXPLAIN again and watch rows collapse and type change from ALL to ref.

Indexing isn't a dark art. It's telling the database the shape of the questions you're going to ask, so it can prepare an answer instead of searching from scratch every time. Read your slow query log, EXPLAIN the top offenders, and add the specific index each one is begging for. Then stop — the goal is the indexes your queries actually use, not the most indexes you can fit.