Database Indexing for Laravel Apps at Scale

The most common Laravel performance incident is not a framework problem. It is a query that scans an entire table, ran fine for a year on small data, and became a wall the moment the table grew. Indexing is how you fix it — but only if you index the right thing, in the right order, for the right reason.

Find the query before you touch anything

Guessing which query is slow wastes days. Turn on the slow-query log, or use the database’s own statistics view, and let the data name the culprit. It is almost always a small number of queries doing almost all the damage, and they are rarely the ones you would have guessed.

Measure first. The query you assume is slow and the query that is actually slow are usually different queries.

Read the plan, then decide

Run EXPLAIN on the offending query. A sequential scan over a large table where you expected an index lookup is the signal. Add an index only when the plan shows it is needed — every index speeds reads and slows writes, so an unused index is pure cost.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- Seq Scan on orders  -> you want an Index Scan here

Composite index column order matters

A multi-column index is not a set; it is ordered, and the order decides which queries it can serve. The rule of thumb: equality columns first, then the range or sort column. For the query above, an index on (customer_id, status, created_at) serves the two equality filters and then the ordered limit — one index doing the whole job.

• Put columns used with = before columns used with ranges or ORDER BY
• A leading column the query does not filter on wastes the index
• One well-ordered composite index often replaces three single-column ones
• Index the columns in your WHERE and join conditions, not every column

N+1 is not an indexing problem

If a page fires one query to list orders and then one more per order to fetch its customer, no index will save you — the problem is the number of round trips, not the speed of each. That is eager loading’s job. Indexing and N+1 are different diseases with different cures, and confusing them is why people add indexes that change nothing.

// N+1: one query per order for the customer
$orders = Order::all();
foreach ($orders as $o) { echo $o->customer->name; }

// fixed: one extra query total
$orders = Order::with('customer')->get();

Why staging lies

Staging has a few thousand rows, so a full table scan finishes instantly and every query looks fast. Production has millions, and the same scan is a timeout. The lesson is to test performance against production-scale data, or at least to read query plans rather than trusting wall-clock time on a tiny dataset. A plan that says “sequential scan” is a warning even when the query is currently fast.

The short version

Log slow queries, EXPLAIN the worst ones, add composite indexes with equality columns first, and fix N+1 with eager loading rather than with indexes. Do that and most “we need a bigger database” conversations turn into a handful of well-chosen indexes and a couple of query changes.