A Practical Guide to Database Indexing in Laravel

Your queries work fine in development. Fifty rows, instant responses, no complaints. Then you deploy to production, real data piles up, and a page that loaded in 200ms takes four seconds.

The problem usually isn't your Eloquent code. It's the indexes you forgot to add.

I've seen this pattern dozens of times across client projects and my own applications. A table grows past 50K rows, and queries that never needed optimization start dragging. The fix is almost always the same: figure out which columns need indexes, add a migration, deploy. Five minutes of work for a 99% performance improvement.

This guide covers what I do when I find slow queries in Laravel. No database theory lectures. Just the practical steps: what to index, what to skip, how to verify it's working, and the traps Laravel doesn't warn you about.

Think of an index like a book's index. Without it, finding every mention of "authentication" means reading the entire book page by page. With it, you flip to the back, find the page numbers, and go straight there.

Database indexes work the same way. Without an index, MySQL scans every row in the table to find matches. With one, it uses a sorted data structure (a B-tree) to jump directly to the matching rows.

The difference is dramatic. A query scanning 500K rows without an index can take over 3 seconds. Add the right index, and the same query takes 12 milliseconds. Not a typo.

Laravel gives you several ways to add indexes. Here are the ones you'll actually use:

// Single-column index
$table->index('email');

// Composite index (multiple columns)
$table->index(['user_id', 'created_at']);

// Unique index (also enforces uniqueness)
$table->unique('email');

// Chain it on column definition
$table->string('email')->unique();

// Full-text index (for search)
$table->fullText('body');

// Expression index (for computed values)
$table->rawIndex('(LOWER(email))', 'users_email_lower_index');

Adding an index to an existing table is a one-migration job:

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

Nothing complicated here. The hard part isn't the syntax. It's knowing which columns need an index in the first place.

This one catches people off guard. When you use foreignId('user_id')->constrained(), does Laravel create an index?

It depends on your database.

foreignId() itself just creates an unsigned BIGINT column. No index, no constraint. When you chain constrained(), Laravel adds a foreign key constraint, and what happens next depends on the engine.

MySQL (InnoDB) automatically creates an index on the foreign key column when the constraint is added. You don't need to add one manually. InnoDB requires indexes on foreign key columns and creates them if they don't exist.

PostgreSQL does not. The constraint is added, but no index is created. Every query joining on that column does a sequential scan. If you're on PostgreSQL, always add an explicit index:

$table->foreignId('user_id')->constrained();
$table->index('user_id'); // Required for PostgreSQL

And if you're using unsignedBigInteger() without a foreign key constraint (common in older codebases), you have no index on any database. Add one.

Any column you frequently filter by should have an index:

// These queries benefit from an index on 'status'
Order::where('status', 'pending')->get();
Order::where('status', '!=', 'cancelled')->count();

The more rows the table has, the bigger the impact. On a 10K-row table, the difference might be negligible. On 500K rows, it's the difference between seconds and milliseconds.

Sorting without an index forces MySQL to load all matching rows into memory and sort them there. EXPLAIN shows this as "Using filesort" in the Extra column. With an index on the sort column, MySQL reads rows in order directly from the index.

// An index on 'created_at' helps here
Order::where('user_id', $userId)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

Even better: a composite index on ['user_id', 'created_at'] lets MySQL filter and sort from the index alone, without touching the actual table rows.

This is where most developers get it wrong. A composite index on (a, b, c) is not the same as three separate indexes on a, b, and c.

A composite index works like a phone book sorted by last name, then first name, then city. You can look up everyone named "Smith," or "John Smith," or "John Smith in Denver." But you can't skip ahead to look up everyone in Denver, because the book isn't sorted by city first.

This is called the left-prefix rule. The index is usable only from left to right:

// ✅ Uses the index (a, b, c)
->where('a', 1)
->where('a', 1)->where('b', 2)
->where('a', 1)->where('b', 2)->where('c', 3)

// ❌ Cannot use the index
->where('b', 2)                          // Skips 'a'
->where('c', 3)                          // Skips 'a' and 'b'
->where('b', 2)->where('c', 3)          // Skips 'a'

There's a subtlety that trips up even experienced developers: MySQL stops using a composite index after the first range condition.

// Index: (status, created_at, amount)

// ✅ Uses all three columns
->where('status', 'active')
->where('created_at', '2025-01-15')
->where('amount', '>', 100)

// ⚠️ Only uses 'status' and 'created_at'
->where('status', 'active')
->where('created_at', '>', now()->subDays(30))
->where('amount', 500)
// 'created_at' is a range condition, so MySQL stops there

The rule: put equality columns (=, IN) first in your index definition, range columns (>, <, BETWEEN) last.

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, and DELETE must update every index on the table. On a table with five indexes, inserts can be 50-80% slower compared to zero indexes.

Skip indexes when:

The column has low cardinality. A gender column with three possible values? An index barely helps. MySQL's optimizer might ignore it entirely and scan the table, since the index would match too many rows to be useful.

The table is small. Under 10K rows, a full scan is often faster than an index lookup. The overhead of maintaining the index isn't worth it.

The column is rarely queried. If a column only appears in SELECT lists (not in WHERE, JOIN, or ORDER BY), an index on it is pure overhead.

You're doing bulk inserts. During large data imports, consider dropping non-essential indexes, inserting the data, then recreating them. The import will finish in a fraction of the time.

Laravel has a built-in way to see how MySQL plans to execute your queries. The explain() method has been available since Laravel 8:

User::where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->explain()
    ->dd();

This runs EXPLAIN on the query and dumps the result. Here's what to focus on:

type is the most important column. From best to worst:

If you see ALL, the query has no useful index.

key shows which index MySQL actually chose. NULL means no index was used.

rows is MySQL's estimate of how many rows it needs to examine. Lower is better. If this number is close to your total row count, you're doing a full scan.

Extra has useful flags:

• "Using index" means all data came from the index without touching the table. Ideal.
• "Using filesort" means MySQL sorted results in memory. Consider indexing the ORDER BY column.
• "Using temporary" means a temp table was created, common with GROUP BY on unindexed columns.

My typical debugging workflow: find the slow query, run ->explain(), check the type and rows columns, add the missing index in a migration, run EXPLAIN again to confirm.

A few common patterns silently skip index creation. You won't notice until the table grows.

Every model using the SoftDeletes trait appends whereNull('deleted_at') to every query. But softDeletes() in the migration just creates a nullable timestamp column. No index.

On a 100K-row table where 5% of rows are soft-deleted, every single read query scans the full deleted_at column. Fix it:

$table->softDeletes();
$table->index('deleted_at');

This is the single most common missing index I find when reviewing Laravel applications.

If you use $table->morphs('commentable'), you're fine. Laravel creates both columns and a composite index on [commentable_type, commentable_id] automatically.

But if someone created the columns manually:

// ❌ No index created
$table->string('commentable_type');
$table->unsignedBigInteger('commentable_id');

You need to add it yourself:

$table->index(['commentable_type', 'commentable_id']);

MySQL can't index a JSON column with a regular $table->index(). If you're querying JSON data with ->where('meta->color', 'red'), no standard index can help.

The cleanest workaround is a virtual generated column:

$table->string('color')
    ->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(meta, '$.color'))");
$table->index('color');

This extracts the value into a virtual column (computed on read, no additional column storage) and indexes it. The index itself takes space like any other index, but the column doesn't bloat your table rows. Your query changes from where('meta->color', 'red') to where('color', 'red'), and now it hits an index.

MySQL 8.0.13 introduced functional indexes that can do something similar under the hood, but they require wrapping JSON expressions in CAST() and the syntax is less transparent. The virtual column approach works on MySQL 5.7+ and gives you a real column you can see and query directly.

Take a table with around 500K order rows. A dashboard page shows recent orders filtered by status and sorted by date:

Order::where('status', 'pending')
    ->orderBy('created_at', 'desc')
    ->limit(20)
    ->get();

Before indexing, EXPLAIN shows:

• type: ALL (full table scan)
• rows: ~487,000
• Extra: Using where; Using filesort
• Query time: ~3.2 seconds

After adding one composite index:

$table->index(['status', 'created_at']);

EXPLAIN now shows:

• type: ref
• rows: ~12,400
• Extra: Using index condition
• Query time: ~18ms

A 99.4% improvement from a single migration.

Indexes make each query faster. But if your page fires 200 queries because of N+1 loading, fast individual queries still add up to a slow page.

Laravel 12.8 introduced automatic relationship loading. Instead of manually specifying with() on every query, you can let Laravel batch-load relations as they're accessed:

$orders = Order::all()->withRelationshipAutoloading();

foreach ($orders as $order) {
    // Laravel auto-loads 'customer' for all orders at once
    echo $order->customer->name;
}

Or enable it globally in your AppServiceProvider:

Model::automaticallyEagerLoadRelationships();

Fewer queries (eager loading) plus faster queries (indexes) is the combination that makes Laravel applications fast.

Next time you create a migration or debug a slow page, run through this:

1. Every foreignId() column has an index (especially on PostgreSQL)
2. Columns in your most common WHERE clauses are indexed
3. Columns in ORDER BY have indexes, or are part of a composite with the WHERE columns
4. Composite indexes put equality columns first, range columns last
5. deleted_at is indexed on any large table using soft deletes
6. Polymorphic columns were created with morphs(), not manually

And when something feels slow, reach for ->explain() before guessing. The answer is usually right there.