6 Eloquent Patterns That Silently Break MySQL Index Usage (And How to Fix Each One)

Originally published at hafiz.dev

---

You added the index. You confirmed it's there with SHOW INDEX FROM orders. The query is still timing out in production, against the same created_at column you've had indexed for months.

This happens more than it should. MySQL's B-tree index is sitting right next to your query, completely ignored, and Eloquent handed it a condition the optimizer can't act on. No error. No warning. No hint in the logs. Just a full table scan against 4 million rows while your dashboard waits.

The reason almost always traces to one concept: sargability.

A predicate is sargable (Search ARGument ABLE) when MySQL can use an index to resolve it directly. The moment a function wraps the indexed column, sargability breaks. MySQL has to compute that function for every single row, then filter the results. That means reading the entire table regardless of what indexes exist.

View the interactive diagram on hafiz.dev

Six Eloquent patterns trigger this silently. You'll recognize at least two of them in your current codebase.

Before going through them, set up EXPLAIN as your verification tool. Wrap any suspicious query like this:

$query = Order::whereDate('created_at', today());
dd($query->toSql(), DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings()));

In the EXPLAIN output, the fields that matter most are type and rows. A type: ALL result means a full table scan. A type: range or type: ref means an index is in use. The rows column tells you how many rows MySQL examined. On a table with 4 million rows, rows: 4000000 next to type: ALL is the confirmation you're looking for.

Two more fields worth checking: key shows which index MySQL actually used (it'll be NULL on a full scan), and Extra shows Using filesort or Using temporary when MySQL had to do extra work outside the index. Any of those three signals in a single EXPLAIN row means you have a query worth fixing.

Laravel Telescope is the easiest way to surface slow query candidates without configuring MySQL's slow query log. It logs every query with execution time. Sort by duration to find the biggest problems first.

If you want to understand how to build the right indexes in the first place, the Laravel database indexing guide covers composite indexes, covering indexes, and when index cardinality matters.

Pattern 1: whereDate() and its siblings

This is the most documented one, and still the least-fixed. The whereDate() method generates DATE(created_at) = '2026-05-22'. That DATE() wraps your column, sargability is gone, and the index on created_at does nothing.

// Bypasses index on created_at
Order::whereDate('created_at', today())->get();

// What MySQL actually runs:
// WHERE DATE(created_at) = '2026-05-22'
// EXPLAIN type: ALL, rows: 4,893,201

The same is true for whereMonth(), whereYear(), whereDay(), and whereTime(). Every one of them wraps the column in a MySQL function before comparing.

A common place this compounds: reporting queries that filter by year and month separately.

// Two function wraps, both non-sargable
Order::whereYear('created_at', 2026)
     ->whereMonth('created_at', 5)
     ->get();

// MySQL runs: WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 5
// EXPLAIN type: ALL, rows: 4,893,201

Both conditions examine every row. The fix is still a range:

// Uses index on created_at
Order::whereBetween('created_at', [
    today()->startOfDay(),
    today()->endOfDay(),
])->get();

// What MySQL actually runs:
// WHERE created_at >= '2026-05-22 00:00:00' AND created_at <= '2026-05-22 23:59:59'
// EXPLAIN type: range, rows: 183

If you're ranging across multiple days, prefer tomorrow()->startOfDay() as the exclusive upper bound instead of endOfDay(). The endOfDay() helper returns 23:59:59, which can miss rows with microsecond timestamps right at midnight.

One note about PostgreSQL: it supports functional indexes, so you can create an index directly on DATE(created_at) and whereDate() will use it. MySQL has no real equivalent. For MySQL apps, the range approach is the correct fix regardless of database.

Pattern 2: LIKE with a leading wildcard

Every developer knows LIKE '%value%' is slow. What's less obvious is exactly why.

MySQL's B-tree index stores values in lexicographical order, like a phone book. A leading % tells MySQL "I don't know how this string starts," so the index is useless. MySQL has to read every row and apply the pattern match manually, front to back.

A trailing wildcard without a leading one is fine: LIKE 'value%' can use a B-tree index because the starting characters are known. The problem is specifically a % at the start.

// Can't use index on email
User::where('email', 'LIKE', '[e-mail]')->get();

// EXPLAIN type: ALL, rows: 2,100,000

You have three practical options depending on what you're actually doing:

Option A: Add a separate indexed column for the thing you're filtering on. If you're always filtering by email domain, store it explicitly:

// Migration: $table->string('email_domain')->index();
User::where('email_domain', 'company.com')->get();
// EXPLAIN type: ref, rows: 14

Option B: Use a FULLTEXT index for text search within a column:

// Requires: $table->fullText('bio');
User::whereFullText('bio', 'laravel developer')->get();

FULLTEXT search is handled by MySQL's inverted index, which is built specifically for text matching. It doesn't do exact substring matches but it handles word-based search well.

Option C: Move to a search engine like Meilisearch or Algolia for any real search feature. Laravel Scout wraps the integration cleanly and the performance difference is not comparable to anything MySQL can do natively.

Pattern 3: Functions in orderByRaw()

This one catches developers because it doesn't cause a slow WHERE filter. It causes a slow sort. When you apply a function to a column in ORDER BY, MySQL falls back to a filesort on the full result set rather than using the index for sorting.

// Triggers filesort
User::orderByRaw('LOWER(name) ASC')->get();

// EXPLAIN Extra: Using filesort

A filesort uses memory for smaller result sets and disk for larger ones. On a table with millions of rows, it's consistently slow.

For case-insensitive sorting, the cleanest fix is using the right collation on the column. Most Laravel apps default to utf8mb4_unicode_ci, which is already case-insensitive for comparisons and sorting. That means a plain orderBy('name') is already case-insensitive if your column uses that collation. No LOWER() needed at all.

If you're on MySQL 8.0.13+ and actually need a functional index for a custom sort expression, you can create one:

-- Functional index on MySQL 8.0.13+
ALTER TABLE users ADD INDEX idx_name_lower ((LOWER(name)));

Then your orderByRaw('LOWER(name)') query will use it. The extra parentheses around LOWER(name) in the DDL are required syntax for functional indexes in MySQL 8.

Pattern 4: Type mismatch on string columns

This is the sneakiest one, and Laravel's query builder documentation explicitly warns about it.

MySQL and MariaDB automatically typecast values during string-to-integer comparisons. Non-numeric strings convert to 0. So when you write:

// status is a varchar column: 'pending', 'processing', 'failed'
Order::where('status', 0)->get();

MySQL casts every status value to an integer before comparing. 'pending' becomes 0. 'processing' becomes 0. 'failed' becomes 0. Every non-numeric string matches, the result is wrong, and because the implicit conversion breaks sargability, you get a full table scan on top of it.

You get bad data silently along with bad performance. That's the double hit.

// Correct: compare string to string
Order::where('status', 'pending')->get();

If you're accepting filter values from a request, cast before querying:

Order::where('status', (string) $request->input('status'))->get();

This also matters inside local scopes. A scope that accepts a $value parameter and passes it directly to where() without type-checking produces this problem invisibly every time it's called with an integer.

// Dangerous scope: no type check
public function scopeWithStatus(Builder $query, $status): Builder
{
    return $query->where('status', $status);
}

// Caller passes an int from a form request: implicit cast happens silently
Order::withStatus($request->input('status_id'))->get();

Add an explicit cast inside the scope so the problem can't sneak through regardless of what the caller passes:

public function scopeWithStatus(Builder $query, mixed $status): Builder
{
    return $query->where('status', (string) $status);
}

Pattern 5: whereRaw() with function wraps

Developers who've heard about whereDate() sometimes switch to whereRaw() thinking they're writing safer SQL. They're not. You can reproduce the exact same non-sargable query yourself.

// Still bypasses index on created_at
Order::whereRaw('YEAR(created_at) = ?', [2026])->get();

// Still bypasses index on shipped_at
Order::whereRaw('DATE(shipped_at) >= ?', ['2026-01-01'])->get();

// Still bypasses index on id
User::whereRaw('CAST(id AS CHAR) = ?', [$stringId])->get();

Any function that wraps an indexed column in a WHERE clause breaks sargability, whether Eloquent generates it or you write it manually. The optimizer sees the function and can't use the B-tree index. That's the rule.

The year-based filtering fix uses a Carbon range:

use Carbon\Carbon;

// Full year range: sargable
Order::whereBetween('created_at', [
    Carbon::create(2026)->startOfYear(),
    Carbon::create(2026)->endOfYear(),
])->get();

For the CAST(id AS CHAR) case, the right fix is not to cast the column. Fix the type upstream. Cast the incoming string to an integer before passing it to the query. The database column should never need to change types mid-query just because the application passed the wrong type.

Pattern 6: orWhere() on a composite index without grouping

This one is less about function wrapping and more about how MySQL resolves OR conditions against composite indexes.

Say you have a composite index on (user_id, status) and you write:

Order::where('user_id', $userId)
     ->orWhere('status', 'urgent')
     ->get();

MySQL generates WHERE user_id = X OR status = 'urgent'. The first condition can use the composite index because user_id is the leftmost prefix. But the second condition needs to find all rows where status = 'urgent' regardless of user_id, which points to a completely different part of the index. MySQL often decides a full table scan is cheaper than doing two separate index lookups and merging the results.

// On a table with 2M rows
// EXPLAIN type: ALL, rows: 1,987,432

MySQL's Index Merge optimization can sometimes handle OR conditions, but it's unreliable. Merging two result sets adds CPU overhead, and the optimizer frequently skips it.

Group your OR conditions explicitly using closures so the query intent is clear:

Order::where(function ($query) use ($userId) {
    $query->where('user_id', $userId)
          ->where('status', '!=', 'closed');
})->orWhere(function ($query) {
    $query->where('priority', 'high')
          ->whereNotNull('escalated_at');
})->get();

For cases where both branches are hitting large portions of the table, two separate queries with a union often outperform a single OR query. Each query can use its own index cleanly, and there's no merge overhead:

$myOrders = Order::where('user_id', $userId)->select('id', 'status', 'created_at');
$urgentOrders = Order::where('status', 'urgent')->select('id', 'status', 'created_at');

$results = $myOrders->union($urgentOrders)->orderBy('created_at', 'desc')->get();

The union approach works best when the two sets don't overlap much. If they do overlap heavily, add unionAll() to skip the deduplication cost, since UNION by default removes duplicate rows through a sort pass.

One thing to watch: union queries return results in an undefined order unless you explicitly call orderBy() on the outer query. Add it when the result order matters to the caller.

Quick reference

Pattern	What MySQL runs	Index?	Fix
whereDate('col', $date)	DATE(col) = ?	No	Half-open range with whereBetween()
LIKE '%value' or '%value%'	Full wildcard scan	No	Separate column, FULLTEXT, or Scout
orderByRaw('LOWER(col)')	Filesort	No	Column collation or functional index
where('varchar_col', 0)	Implicit cast	No	Compare with correct string type
whereRaw('YEAR(col) = ?')	Same as whereDate	No	Carbon range with whereBetween()
->where()->orWhere() on composite	OR breaks range scan	Partial	Grouped closures or union queries

FAQ

How do I find these patterns in an existing app?

Enable MySQL's slow query log and set long_query_time to something low like 0.1 seconds. Then run EXPLAIN on anything that shows up. Look for type: ALL in the output. Laravel Telescope surfaces the generated SQL for every query, which makes it easy to copy into a MySQL client and run EXPLAIN manually. The Laravel query optimization guide walks through the debugging workflow in more detail.

Does this apply to local scopes?

Yes, completely. A local scope that calls whereDate() or uses a leading wildcard LIKE generates the exact same SQL. The ORM layer doesn't change what MySQL executes. Always check the generated SQL with ->toSql() before assuming a scope is index-friendly.

Is whereDate() ever acceptable?

On small tables, under roughly 50,000 rows, the performance difference is imperceptible. The problems start when the table crosses a few hundred thousand rows. The range fix costs nothing extra, so there's no good reason not to use it regardless of table size.

What about PostgreSQL?

PostgreSQL supports functional indexes, so you can create an index on DATE(created_at) directly and whereDate() will use it. MySQL has no equivalent without generated columns, which add schema overhead. For MySQL apps, the range approach is always the cleaner fix.

Why doesn't Eloquent warn you about these patterns?

Because Eloquent doesn't know your schema. It doesn't know which columns are indexed, what types they are, or how many rows your table has. The ORM's job is to generate valid SQL. Understanding query plans is yours, and EXPLAIN is the tool for it.

Closing thoughts

Most of these patterns look completely reasonable when you write them. That's what makes them dangerous. The fix isn't complicated in any of the six cases. It's usually a two-line change, but you have to know to look for it.

The underlying rule is always the same: if a function wraps your indexed column in the WHERE or ORDER BY clause, MySQL can't use the index. Once you've internalized that, you'll spot non-sargable queries anywhere, not just in the six patterns above. It changes how you read Eloquent code, how you review PRs, and how you respond when someone reports a slow page. Instead of reaching for a new server or a caching layer first, you run EXPLAIN.

A lot of Laravel performance problems that get blamed on infrastructure are actually query problems. The query optimizer doesn't care that you're using Eloquent. It doesn't know you expected the index to be used. It just follows the rules of the SQL it receives. Writing sargable queries is how you work with the optimizer rather than around it.

Building something and want a second set of eyes on the query layer before it becomes a problem at scale? Get in touch.