You added an index on created_at. The daily report still gets slower as the table grows. EXPLAIN shows a full scan. The index is sitting right there.

MySQL is not ignoring you. You asked about DATE(created_at), not created_at. Those are different values, and a normal B-tree on the raw column does not store the first one. MySQL will not rewrite that into a range for you.

(MariaDB 11.1 and later can rewrite DATE(col) and YEAR(col) comparisons into ranges automatically. Everything below is about MySQL, and the range form is still the portable version.)

The helper writes the slow SQL

Laravel makes this easy to do by accident. whereDate() reads like the grown-up way to filter a timestamp to a single day:

$orders = Order::query()
    ->whereDate('created_at', $date)
    ->get();

What actually hits the database is:

SELECT * FROM orders
WHERE DATE(created_at) = '2026-11-08';

whereYear(), whereMonth(), whereDay(), and whereTime() do the same kind of thing with YEAR(), MONTH(), DAY(), and TIME(). Combining them does not help:

Order::query()
    ->whereYear('created_at', 2026)
    ->whereMonth('created_at', 11)
    ->get();

That becomes WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 11. Two function calls, and MySQL still can't seek the created_at index with either one.

The inequality form has the same problem. This looks like a range. It is still a function around the column:

Order::query()
    ->whereDate('created_at', '>=', '2026-10-01')
    ->get();
 
// WHERE DATE(created_at) >= '2026-10-01'

Fine on a small table. On a growing orders or events table, MySQL has to compute the function for every row it reads, so the plan is a full scan (or a full index scan) instead of a seek.

Compare the column, not a function of the column

A day is a half-open range: at or after midnight, and before midnight tomorrow. Leave created_at bare and put the work on the bounds:

$start = \Illuminate\Support\Carbon::parse($date, config('app.timezone'))->startOfDay();
$end = $start->copy()->addDay();
 
$orders = Order::query()
    ->where('created_at', '>=', $start)
    ->where('created_at', '<', $end)
    ->get();

Now the SQL looks like this:

SELECT * FROM orders
WHERE created_at >= '2026-11-08 00:00:00'
  AND created_at <  '2026-11-09 00:00:00';

The column is untouched. The optimizer can now seek the created_at index as a range. Whether it does is a cost decision, covered below.

Prefer < $end over endOfDay(). endOfDay() lands on 23:59:59.999999, and if anything along the way drops the fractional seconds you can miss rows in the last second. whereBetween() is inclusive on both ends, so passing tomorrow's midnight as the upper bound would also pick up the first instant of the next day. Half-open ranges do not have that argument.

If created_at is stored in UTC (it should be) and "the 8th" means the 8th in America/New_York, compute both bounds in that timezone before you compare:

$start = \Illuminate\Support\Carbon::parse($date, 'America/New_York')->startOfDay()->utc();
$end = \Illuminate\Support\Carbon::parse($date, 'America/New_York')->addDay()->startOfDay()->utc();

A UTC calendar day and a New York calendar day are not the same window. The range still works. The bounds just have to mean the day you think they mean.

The same pattern in TypeScript

Prisma will not wrap the column if you give it a range. gte and lt map to >= and < on created_at:

const start = new Date('2026-11-08T00:00:00.000Z');
const end = new Date('2026-11-09T00:00:00.000Z');
 
const orders = await prisma.order.findMany({
  where: {
    createdAt: {
      gte: start,
      lt: end,
    },
  },
});

Same rule as PHP: those Date values should be the instants that start and end the day in the timezone you mean. Watch out for new Date('2026-11-08'): a date-only string is parsed as UTC midnight by the spec, not local midnight. That's predictable, but it's the wrong instant if your day is New York's.

The thing to avoid is sending the function to MySQL yourself:

const orders = await prisma.$queryRaw`
  SELECT * FROM orders
  WHERE DATE(created_at) = ${date}
`;

That raw query has the same scan as whereDate(). The ORM did not save you, because you walked around it.

It is not just dates

Any function around the column can keep a normal B-tree on the raw column from doing a seek.

LOWER(email) is a frequent one:

User::query()
    ->whereRaw('LOWER(email) = ?', [strtolower($email)])
    ->first();

If the column already uses a case-insensitive collation (utf8mb4_unicode_ci, utf8mb4_0900_ai_ci, and most other _ci collations), LOWER() is extra work and it can prevent a seek on the plain email index. Store the email normalized, or compare against the bare column and let the collation do the matching:

User::query()
    ->where('email', strtolower($email))
    ->first();

Leading wildcards are the same family of mistake. WHERE title LIKE 'launch%' can use a B-tree on title. WHERE title LIKE '%launch%' cannot seek by that prefix, because the index is sorted by prefix and you threw the prefix away. If what you actually want is word search, a FULLTEXT index or a real search engine is the tool. That matches tokens, not arbitrary substrings, so it is not a drop-in replacement for %launch%. Decide which one the feature actually needs.

The short version: if you have to transform something, transform the value you pass in. Leave the column alone.

If you cannot change the query

An index on the expression itself can be an option in MySQL versions that support functional indexes. It is a separate index with its own storage and write cost, and not every expression or column type is eligible. Check those restrictions before treating it as a drop-in fix.

If you own the query, start with the range rewrite. You may not need a second index to paper over a helper.

Check with EXPLAIN

Do not argue with the query planner from memory. Ask it:

EXPLAIN SELECT * FROM orders
WHERE DATE(created_at) = '2026-11-08';
 
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-11-08 00:00:00'
  AND created_at <  '2026-11-09 00:00:00';

The wrapped version typically shows type: ALL and key: NULL, or type: index (a full walk of an index instead of a seek). For a narrow window, the range version should show type: range with key on your created_at index.

If it doesn't, check the boring stuff first: wrong column, missing index, another filter driving the plan. And remember the optimizer can pick a full scan on purpose. If your range covers a big slice of the table, reading it straight through can be cheaper than thousands of index lookups. That's a cost call, not proof the rewrite failed. Try a one-day window to confirm the index is usable.

Rule of thumb

In MySQL, if the column is inside a function in the WHERE clause, a plain index on that column usually can't do a seek. Give the database a range or an equality on the bare column. whereDate() is convenient. Convenient is not the same as cheap.