Laravel雄辩的withCount()应该比with()慢 [英] Laravel eloquent withCount() supposed to be slower than just with()

问题描述

所以我问这的原因是，在我当前的应用程序 withCount()中，响应时间几乎是原来的三倍，而不是通过 with()来获取关系的所有数据.并从前端获取长度(javascript).我以为使用 withCount()的目的是加快查询速度，但也许我错了?

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?

例如:

courseSession::where('id', '>=', 1)
->where('id', '<=', 320)
->withCount('enrollments')
->get();

平均约900毫秒响应(调试栏将其显示为单个DB调用)

averages around 900ms response (debugbar shows this as a single DB call)

但是

courseSession::where('id', '>=', 1)
->where('id', '<=', 320)
->with('enrollments')
->get();

获得约350ms(Debugbar将其显示为两个不同的数据库调用)

gets around 350ms (Debugbar shows this as two different db calls)

模型关系定义如下:

public function enrollments()
{
   return $this->hasMany(EmployeeEnrollment::class)->where('dropped', '=', null);
}

注意:已经检查过，如果我删除where子句，它只会将其加速30ms

NOTE: already checked that if i remove the where clause it only speeds it up by 30ms

员工注册表大约有11000行，而我正在对其进行查询的模型的表大约有2k行

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

推荐答案

区别是您要mysql计数语句中的每一行，而不仅仅是转储.withCount的意义并不在于它速度更快，如果您只需要计数，它在内存上就会更好.

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.

例如，我与数以千计的数据丰富的项目有关系.如果我只是使用 with 然后尝试计算结果，php 将耗尽内存，我有机会进行计数.如果我使用withCount，则仅返回一项，告诉我有多少项，因此php内存无需缴税，但是所有工作都在sql服务器上完成.

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.

这篇关于Laravel雄辩的withCount()应该比with()慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！