So the reason i ask this is that in my current app withCount() almost triples the response time vs just fetching all the data for the relationship via with() and just getting the length from the frontend (javascript). I thought the point of using withCount() was to speed up the query but maybe i’m wrong?
for example:
courseSession::where('id', '>=', 1)
->where('id', '<=', 320)
->withCount('enrollments')
->get();
averages around 900ms response (debugbar shows this as a single DB call)
but
courseSession::where('id', '>=', 1)
->where('id', '<=', 320)
->with('enrollments')
->get();
gets around 350ms (Debugbar shows this as two different db calls)
Model Relationship is defined as follows:
public function enrollments()
{
return $this->hasMany(EmployeeEnrollment::class)->where('dropped', '=', null);
}
NOTE: already checked that if i remove the where clause it only speeds it up by 30ms
Employee Enrollment Table is around 11k rows and the table the model i’m running query on is around 2k rows
This is also on a local dev env and tested on two separate machines
I’m kinda clueless about db stuff so i’m not sure if i’m doing something wrong… but any help on this would be appreciated
Advertisement
Answer
The difference is you’re asking mysql to count every row in your statement instead of just dump. The point of withCount is not that it’s faster, its better on memory if all you need is a count.
For example, I have a relation with thousands of data rich items. If I just use a with and then try to count the results, php will run out of memory I ever get the chance to do the count. If I use withCount, only one item is returned that tells me how many items there are, so there’s no tax on php memory, but all the work is done on the sql server.