# A Practical Guide to Database Indexing in Laravel

**Author:** Mozex | **Published:** 2026-04-09 | **Tags:** Laravel, PHP, Tutorial, Performance, Database | **URL:** https://mozex.dev/blog/14-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.

<!--more-->

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.

## The Basics (Quick Version)

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.

## Index Syntax in Laravel Migrations

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

```php
// 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:

```php
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.

## What to Index

### Foreign Keys

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:

```php
$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.

### Columns in WHERE Clauses

Any column you frequently filter by should have an index:

```php
// 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.

### Columns in ORDER BY

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.

```php
// 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.

## Composite Indexes and Column Order

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:

```php
// ✅ 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.

```php
// 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.

## What NOT to Index

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.

## Reading EXPLAIN Output

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

```php
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:

| Type | What it means |
|------|-------------|
| `const` | Found exactly one row via primary key |
| `eq_ref` | Used a unique index to find one row per join row |
| `ref` | Used a non-unique index to find matching rows |
| `range` | Used an index for a range scan (>, <, BETWEEN) |
| `index` | Full index scan (reads every entry in the index) |
| `ALL` | Full table scan. No index used. This is the red flag. |

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.

## The Indexes Laravel Doesn't Create for You

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

### Soft Deletes

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:

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

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

### Manual Morph Columns

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:

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

You need to add it yourself:

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

### JSON Column Queries

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:

```php
$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.

## Measuring the Difference

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

```php
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:

```php
$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.

## One More Thing: Fewer Queries, Not Just Faster Ones

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:

```php
$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`:

```php
Model::automaticallyEagerLoadRelationships();
```

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

## The Checklist

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.