Laravel Eloquent: Why Your Database Index Is Being Ignored

TL;DR: Adding a composite index like$table->index(['status', 'created_at']) will NOT optimize queries that filter solely by created_at. Under the Leftmost Prefix Rule, databases sort composite B-trees by the first column first. To leverage the index, your query must filter starting from the very first indexed column, and equality filters must precede range operators.

1. The Real-World Developer Trap

Picture this: your Laravel application's orders table has grown to 500,000 rows. Dashboard reports are starting to lag, so you decide to be proactive and create a composite index in your migration:

// database/migrations/2026_11_06_create_orders_table.php
Schema::table('orders', function (Blueprint $table) {
    // Adding a composite index on status and created_at
    $table->index(['status', 'created_at'], 'orders_status_created_at_idx');
});

You run php artisan migrate, deploy to production, and feel satisfied.

A few hours later, a developer writes an Eloquent query to fetch recent orders created within the last 7 days:

$recentOrders = Order::where('created_at', '>=', now()->subDays(7))->get();

You check your APM logs (or Laravel Telescope), and to your shock: the query takes 1,240 milliseconds and consumes massive disk I/O!

Why didn't MySQL or PostgreSQL use the composite index you just created?

2. Inspecting the Query with EXPLAIN ANALYZE

When in doubt, always ask the query planner what it actually did. If we inspect the raw SQL query with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-10-30 00:00:00';

The output reveals the unpleasant truth:

Notice two critical fields:

• type: ALL— The database performed a Full Table Scan, inspecting all 500,000 rows one by one.

• key: NULL— The database saw orders_status_created_at_idx as a candidate (possible_keys), but decided it was completely useless for this query.

3. Under the Hood: The Leftmost Prefix Rule

To understand why this happens, you have to visualize how relational databases store composite indexes in B-Trees.

The Phonebook Analogy

Imagine a printed physical phonebook. Entries are sorted primarily by Last Name, and secondarily by First Name:

• Query 1: Find people whose Last Name is"Ahmed".
  Super fast! You jump directly to the "A" section.

• Query 2: Find people whose Last Name is"Ahmed"AND First Name is"Farhan".
  Even faster! You binary search straight to the exact entry.

• Query 3: Find all people whose First Name is"Tanvir"(with NO last name given).
  How do you do it? The book is sorted by Last Name. You have to read every single page from cover to cover!

This is the Leftmost Prefix Rule.

A composite index on (column_a, column_b, column_c) can only be used by the B-Tree engine if your query's WHERE clause filters by:

1. (column_a)

2. (column_a, column_b)

If you query (column_b) alone, or (column_b, column_c) without column_a, the database cannot navigate the tree from the root node and must fall back to a full table scan.

graph TD
    Root["B-Tree Root (status)"]
    Root --> Completed["status = 'completed'"]
    Root --> Pending["status = 'pending'"]
    Completed --> C1["created_at: 2026-11-01"]
    Completed --> C2["created_at: 2026-11-02"]
    Pending --> P1["created_at: 2026-11-01"]
    Pending --> P2["created_at: 2026-11-02"]
    
    style Root fill:#1e293b,stroke:#0ea5e9,stroke-width:2px,color:#fff
    style Completed fill:#0f172a,stroke:#22c55e,color:#fff
    style Pending fill:#0f172a,stroke:#eab308,color:#fff

Without specifying status, the database has to visit every branch to check created_at.

4. The Optimal Solution in Laravel

Depending on your access patterns, you have two clear solutions:

Solution A: If You Frequently Filter bycreated_atAlone

Add a dedicated single-column index on created_at:

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

Solution B: If You Filter by Both Columns, Mind the Order!

If your queries usually filter by both status AND created_at:

// Laravel Eloquent Query
$recentCompletedOrders = Order::where('status', 'completed')
    ->where('created_at', '>=', now()->subDays(7))
    ->get();

Your composite index should place the Equality Filter (status = 'completed') BEFORE the Range Filter (created_at >= ...):

Schema::table('orders', function (Blueprint $table) {
    // Status (Equality) first, created_at (Range) second
    $table->index(['status', 'created_at']);
});

Pro Tip: In MySQL and PostgreSQL, once a B-Tree hits a range operator (>,<, BETWEEN, LIKE 'abc%'), it cannot use subsequent index columns for index seeks. Therefore, always put columns evaluated with=before columns evaluated with ranges.

5. Benchmark & Performance Comparison

We executed both queries against an active PostgreSQL / MySQL database populated with 500,000 synthetic e-commerce orders:

The difference between 1.2 seconds and 4 milliseconds under concurrent web traffic is the difference between a crashed server and sub-second API responses.

6. Golden Rules for Composite Indexes

Before committing your next Laravel database migration, run through this checklist:

1. Follow the Leftmost Prefix: An index on (A, B) only works if your query includes A.

2. Order by Equality First, Range Second: Place columns used in= / IN() conditions before columns using>,<, or BETWEEN.

3. Cardinality Matters: Between two equality filters, place the column with higher cardinality (more unique values) first to filter out non-matching rows earlier.

4. Avoid Over-Indexing: Composite indexes take disk space and slow down INSERT, UPDATE, and DELETE queries. Only index columns that appear frequently in slow queries identified by slow query logs.

5. Always Verify with EXPLAIN: Never guess. Run DB::enableQueryLog() or EXPLAIN ANALYZE to prove the index seek is actually triggered.

7. Conclusion & Next Steps

Composite indexes are one of the most powerful tools in backend engineering, but only when you respect how the underlying storage engine traverses trees.

In the next part of our 30 Days of Modern Full-Stack Engineering series, we will tackle the notorious Laravel N+1 Query Problem and how to use with(), load(), and withCount() to eliminate hundreds of hidden queries in your Blade views and API resources.

Found this guide helpful? Subscribe to the KoderSolution YouTube Channel for concise, animated backend breakdowns, and explore our free courses on kodersolution.com.