mysql groupby优化_如何使用GROUPBY MySQL查询优化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;

}

Raw query with Explain:

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;

Output of Explain:

Explain Visual View:

This full table scan of count is making the problem.

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 ;

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

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