Database Indexing for Laravel Developers

Shaz3e blog cover on database indexing for Laravel developers, showing a woman searching a library card catalog drawer as an index metaphor.

A slow Laravel page is usually one query reading far more rows than it returns. Adding the right index fixes it. Adding the wrong one costs disk and write speed and changes nothing. The skill that separates the two is reading EXPLAIN, so this post is built around that: one realistic query, four index choices, and the plan the database produced for each.

It’s the database half of the advice in my Laravel performance guide. If the slowness comes from queries repeating per row rather than a single heavy query, start with N+1 queries instead. And if you don’t yet know which queries are slow, performance monitoring tools comes first.

What this post covers

Tested versions and how I tested

ToolVersion
Laravel Framework13.43.0 (fresh laravel/laravel skeleton)
PHP8.4.27 CLI
PHPUnit12.5.45: 9 index tests, plus the 2 example tests (11 passing, 20 assertions)
Test databaseSQLite in memory, using its EXPLAIN QUERY PLAN
EXPLAIN numbers belowMariaDB 11.4.13 with 300,000 rows (a MySQL-compatible server)

Two things to be straight about. First, the plans in this post come from MariaDB 11.4, not MySQL. The columns and the basic behaviour match for these simple queries, but the optimizers differ in places (I found one below), so run EXPLAIN on your own server before you trust a conclusion. Second, my PHP build had no pdo_mysql, so Laravel never connected to MariaDB. Two small scripts in the sample project print the MySQL-dialect SQL that Laravel’s own grammar generates for the migrations and the queries, and I ran that SQL through MariaDB’s command-line client. The SQLite tests are what run inside Laravel.

The data is synthetic: 300,000 orders across 5,000 customers, generated with a seeded RAND() so the run is repeatable. It isn’t client data.

The query and the table

An orders table, and the page every account has: my latest paid orders.

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('customer_id');
    $table->string('status', 20);
    $table->unsignedInteger('total_cents');
    $table->dateTime('placed_at');
    $table->timestamps();
});

The model has a scope for the query, so the index and the query live next to each other in the codebase:

public function scopeRecentPaid(Builder $query, int $customerId, int $limit = 20): Builder
{
    return $query->where('customer_id', $customerId)
        ->where('status', 'paid')
        ->orderByDesc('placed_at')
        ->limit($limit);
}

That produces:

select * from `orders` where `customer_id` = 1234 and `status` = 'paid' order by `placed_at` desc limit 20;

Reading EXPLAIN in five columns

Put EXPLAIN in front of any SELECT. In Laravel you can call ->explain() on the query builder and get the same rows back. The MySQL reference for EXPLAIN output lists every column; five carry most of the story:

ColumnWhat to look for
typeALL is a full table scan. ref and range use an index. index reads the whole index, which is usually not what you wanted.
keyThe index the optimizer chose. NULL means none.
key_lenHow many bytes of the index it uses. A longer value on the same index means it used more of the columns.
rowsThe optimizer’s estimate of rows it must read. An estimate, not a count.
ExtraUsing filesort means it sorts after fetching the rows instead of reading them in order.

One column is not enough

With no index beyond the primary key, the database has no choice:

type  key   rows    Extra
ALL   NULL  300309  Using where; Using filesort

It reads all 300,000 rows, keeps the paid ones for customer 1234, sorts them, and returns 20. Now add the index most people reach for, on customer_id alone:

type  key                 key_len  rows  Extra
ref   customer_only_idx   8        60    Using where; Using filesort

A big improvement: it jumps straight to that customer’s 60 orders. But Using filesort is still there. The index finds the rows; it has no opinion about their order, so the database sorts them every time. For 60 rows that’s cheap. For a customer with 60,000 orders it isn’t.

Composite index column order

A composite index sorts rows by its first column, then within that by its second, and so on, like a phone book sorted by surname, then first name. The MySQL documentation on multiple-column indexes describes the same idea. The practical rule I use is: equality columns first, then the column you sort or range on, last.

Our query tests customer_id and status for equality and sorts by placed_at, so the index is those three, in that order:

Schema::table('orders', function (Blueprint $table) {
    // Equality columns first, the column we sort or range on last.
    $table->index(['customer_id', 'status', 'placed_at'], 'orders_customer_status_placed_idx');
});
type  key                                key_len  ref          rows  Extra
ref   orders_customer_status_placed_idx  30       const,const  60    Using where

Both equality columns are matched (ref shows two constants), and Using filesort is gone: within one customer and one status, the index is already ordered by placed_at, so the database reads from the end and stops after 20 rows. ANALYZE confirmed it, reading 20 rows.

The wrong order, and why EXPLAIN alone can mislead

Now the same three columns in a different order: (placed_at, customer_id, status). It looks reasonable, and it even avoids the filesort, so I expected a decent plan:

type   key               key_len  rows  Extra
index  placed_first_idx  35       20    Using where

rows 20 looks great. It’s wrong. The optimizer is walking the index in placed_at order from the newest entry, checking each one for customer 1234 and status paid, until it finds 20. MariaDB's ANALYZE statement runs the query and reports what really happened: 99,362 rows read against 20 for the correct index. MySQL 8.0.18 and later have EXPLAIN ANALYZE for the same purpose.

The lesson isn’t just about column order. EXPLAIN shows estimates, and an estimate for a plan that depends on how the data is spread can be badly off. When a query matters, run the version that executes it too.

The leftmost column rule

A composite index is usable from its first column. A query that filters only on status can’t use (customer_id, status, placed_at), because the index isn’t sorted by status first:

select * from `orders` where `status` = 'paid';

type  key   rows    Extra
ALL   NULL  300309  Using where

That’s a full scan, with the index sitting right there. If you need that query too, it needs its own index, and you should ask first whether that query ever runs in a place that matters. The SQLite test for this passes for the same reason: the planner reports a scan.

Functions on indexed columns

Wrapping an indexed column in a function usually stops the database from searching the index by that column. Laravel makes it easy to do by accident: whereYear(), whereMonth() and whereDate() all compile to a function call on the column. The index-friendly version is a range, and the model has a scope for it:

public function scopePaidInMonth(Builder $query, int $customerId, CarbonInterface $month): Builder
{
    return $query->where('customer_id', $customerId)
        ->where('status', 'paid')
        ->where('placed_at', '>=', $month->copy()->startOfMonth())
        ->where('placed_at', '<', $month->copy()->addMonthNoOverflow()->startOfMonth());
}

On MariaDB, for customer 1234 and June 2026:

VersionSQL on the columnkey_lenrows estimate
Rangeplaced_at >= '2026-06-01' and placed_at < '2026-07-01'35 (all three columns)12
Functionyear(placed_at) = 2026 and month(placed_at) = '06'3560

Here the function version still uses the index (it filters by customer and status, then checks the month on the index entries), so the damage is modest: 60 rows against 12. For a customer with a year of busy history it would matter more. Then there was a surprise: whereDate('placed_at', '2026-06-25'), which compiles to date(placed_at) = '2026-06-25', got a range plan estimating 2 rows. MariaDB rewrites that particular pattern into a range. I haven’t verified that MySQL does the same, so I wouldn’t rely on it. Writing the range yourself behaves the same on any server.

The SQLite tests pin the difference down in a way that runs in CI: the range version’s plan mentions placed_at>? AND placed_at<?, and the function version stops at (customer_id=? AND status=?).

The Laravel migration and its tests

The migration above uses $table->index([...], 'name') from the Laravel migrations documentation. Naming the index yourself keeps it stable and easy to reference in a test. The tests read the schema and ask the planner what it does:

class QueryPlan
{
    public static function for(Builder $query): string
    {
        $rows = DB::select('EXPLAIN QUERY PLAN '.$query->toSql(), $query->getBindings());

        return collect($rows)->pluck('detail')->implode(' | ');
    }
}
public function test_migration_creates_the_composite_index_in_the_chosen_order(): void
{
    $index = collect(Schema::getIndexes('orders'))->firstWhere('name', self::INDEX);

    $this->assertNotNull($index);
    $this->assertSame(['customer_id', 'status', 'placed_at'], $index['columns']);
    $this->assertFalse($index['unique']);
}
public function test_with_the_index_the_query_searches_it_and_needs_no_sort_step(): void
{
    $plan = QueryPlan::for(Order::query()->recentPaid(7));

    $this->assertStringContainsString('SEARCH orders USING INDEX '.self::INDEX, $plan);
    $this->assertStringContainsString('customer_id=? AND status=?', $plan);
    $this->assertStringNotContainsString('TEMP B-TREE', $plan);
}

The suite also checks: the query scans without the index; a status-only query doesn’t use it; the month range uses all three columns while the function version stops at two; the range and function versions return the same rows; and rolling the migration back removes the index. SQLite’s planner words things differently from MySQL’s, so these tests protect the intent (the index exists, in this order, and your query can use it) rather than reproduce MariaDB’s output. If someone later reorders the columns in a migration, a test fails.

What an index costs

Indexes aren’t free. On this 300,000-row table the data was 18.5 MB and the composite index added 11.5 MB. Every insert and update that touches those columns has to maintain the index too. So I add an index for a query I can name, I check it with EXPLAIN, and I drop ones nothing uses. In a multi-tenant app, a tenant column is nearly always the first column of the useful indexes; I touched on that in the Laravel multi-tenancy database post.

What I did not test

  • MySQL itself. All EXPLAIN output here is MariaDB 11.4. The optimizer can choose differently on MySQL 8.4, and I only ran the SQLite tests inside Laravel.
  • Production data. The rows are generated, so the spread of customers and dates is smoother than a real orders table.
  • Write cost. I measured index size, not insert speed.
  • Partitioned tables, covering indexes, descending index keys and invisible indexes. All worth knowing, none covered here.

FAQ

Does column order in a composite index matter?

Yes. Put the columns you compare with equality first and the column you sort or range on last. In my test the right order read 20 rows and the wrong order read 99,362 for the same query.

Should I just index every column I filter on?

No. A single-column index on customer_id found the customer’s rows but still had to sort them. One composite index matched to the query did both jobs, and fewer indexes means cheaper writes.

How do I run EXPLAIN in Laravel?

Call ->explain() on a query builder or Eloquent query to get the plan as a collection, or run EXPLAIN in your database client against the SQL from toSql() with the bindings filled in.

Why isn’t my composite index used when I filter on its second column only?

The index is sorted by its first column, then the second within it, so the database can’t search by the second column alone. Filter on the leading column too, or create an index that starts with the column you filter on.

Do whereDate and whereYear stop MySQL from using an index?

They put a function around the column, which usually prevents a direct index search on it. Use a start and end range instead. Some servers rewrite a few patterns (MariaDB rewrote date(col) = value in my test), but a range works the same everywhere.

I’m Shahrukh Akhtar (Shaz3e), CTO & Co-Founder of Diligent Creators Pvt. Ltd., a Technology Executive & Product Builder. I run client delivery and build SaaS products, including EloSync. You can find me on X, LinkedIn, Peerlist and GitHub.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *