如何使用GROUPBY MySQL查询优化COUNT? [英] How can I optimise COUNT with GROUPBY mysql query?

问题描述

我在 eus 表中有 5M 条记录，在 es 表中有 121 条记录.我正在做左联接，但是 COUNT 查询使我的查询非常慢.我该如何优化呢?

I have 5M records in eus table and 121 records in es table. I am doing a left join but the COUNT query is making my query very slow. How can I optimize this?

public static function getAllActiveEvaluationSymptomsWithNameForDataTable(){
    $queryBuilder = new Builder();

    $queryBuilder
        ->from(array('es' =>  static::class))
        ->leftJoin('EvaluationUserSymptom',  'es.id = eus.eb_evaluation_symptom_id','eus')
        ->columns('es.id, es.title, COUNT(eus.eb_evaluation_symptom_id) AS counts')
        ->groupBy('eus.eb_evaluation_symptom_id')
        ->where('es.is_active = 1');

    return  $queryBuilder;
}

带有解释的原始查询:

EXPLAIN 
SELECT  es.id AS id, es.title AS title,
        COUNT(eus.eb_evaluation_symptom_id) AS counts,
        eus.date_created AS date_created
    FROM  eb_evaluation_symptom AS es
    LEFT JOIN  eb_evaluation_user_symptom AS eus
           ON es.id = eus.eb_evaluation_symptom_id
    WHERE  es.is_active = 1
    GROUP BY  eus.eb_evaluation_symptom_id;

说明输出:

解释视觉视图:

此计数的全表扫描引起了问题.

注意:所有JOIN和必要的列字段均具有正确的索引.

Note: All JOINs and necessary columns fields are having proper indexes.

推荐答案

相关子查询可以是一种快速方法:

A correlated subquery can be a fast method:

SELECT es.id, es.title,
      (select count(*)
       from eb_evaluation_user_symptom eus
       where es.id = eus.eb_evaluation_symptom_id
      ) as cnt
FROM eb_evaluation_symptom es  
WHERE es.is_active = 1 ;

为了提高性能，您需要在eb_evaluation_user_symptom(eb_evaluation_symptom_id)上建立索引.

For performance, you want an index on eb_evaluation_user_symptom(eb_evaluation_symptom_id).

eb_evaluation_symptom上的索引不会有太大帮助，因为该表非常小.

An index on eb_evaluation_symptom won't be of much help, because that table is so small.

这篇关于如何使用GROUPBY MySQL查询优化COUNT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！