如何优化symfony原则(mysql)中计数查询的性能

Question

我过去常常在下面的查询中计数行(大约1M条记录和左联接多个表(：

SELECT COUNT(DISTINCT u0_.id) AS sclr_0 
FROM user u0_ 
LEFT JOIN user_detail u1_ ON u0_.id = u1_.user_id 
LEFT JOIN recruitment_info r2_ ON u0_.id = r2_.user_id 
LEFT JOIN user u3_ ON u0_.master_account_id = u3_.id 
LEFT JOIN applicants_partners a4_ ON u0_.id = a4_.applicant_id 
LEFT JOIN partner p5_ ON a4_.partner_id = p5_.id 
WHERE u0_.type <> 'PARTNER' 
AND u0_.is_delete = 0 
ORDER BY u0_.id DESC;

在我的符号中，我得到了总计：$total = $queryBuilder->getQuery()->getSingleScalarResult();它运行良好，但大约花了2.5秒。

所以，我想提高它的性能。我把它改成：

SELECT COUNT(u0_.id) AS sclr_0 
FROM user u0_ 
LEFT JOIN user_detail u1_ ON u0_.id = u1_.user_id 
LEFT JOIN recruitment_info r2_ ON u0_.id = r2_.user_id 
LEFT JOIN user u3_ ON u0_.master_account_id = u3_.id 
LEFT JOIN applicants_partners a4_ ON u0_.id = a4_.applicant_id 
LEFT JOIN partner p5_ ON a4_.partner_id = p5_.id 
WHERE u0_.type <> 'PARTNER' 
AND u0_.is_delete = 0 
GROUP BY u0_.id 
ORDER BY u0_.id DESC;

此处的更改是删除DISTINCT并添加GROUP BY。然后我在符号中应用计数数组结果：

$result = $queryBuilder->getQuery()->getArrayResult();
$total = count($result);

所以总数是正确的，但这次花了大约20秒，天哪。当我尝试在Sequel Pro工具中只运行原始查询时，只花了大约40毫秒。也许getArrayResult()有问题。请帮帮我，谢谢。

Answer 1

正如Akina在评论中提到的那样，您可以通过删除所有左联接、order by甚至distinct来简化此查询。

您的查询将变成这样：

SELECT COUNT(u0_.id) AS sclr_0 
FROM user u0_ 
WHERE u0_.type <> 'PARTNER'
AND u0_.is_delete = 0;

是的，您必须在Doctrine Query实例上使用getSingleScalarResult((。