whereDate is a harmless eloquent method, or is it??
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
When you are querying a table with large data, whereDate
is a bad option.
Reasons
- The query does a full table scan
- It does not allow indexed columns to work.
My Personal Experience
A snippet from my code before
$message_week=Log::where('user_id',$user->id)
->whereDate('created_at','>=',$week_started_date)
->count();
I changed it to this and I had great performance improvement
$date=$week_started_date->format('Y-m-d');
$message_week=Log::where('user_id',$user->id)
->where('created_at','>=',$date.' 00:00:00')
->count();
Take note of the space before the 00 in ' 00:00:00'
It is buiding the date to be 2022-12-31 00:00:00
Stop using whereDate for large data query
Top comments (1)
seem fine but this query will get all Log where is greater than $date, I think to get only Log on that $date, better to use whereBetween right?