How can I optimise COUNT with GROUPBY mysql query?

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.

Answer

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.

Advertisement

Answer