We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

How can I optimise this 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;
}


2.8k

have you pulled the raw query and run an explain on it?



145.0k

Add indexes on es.id, eus.eb_evaluation_symptom_id, es.is_active.



5.8k
in reply to Wojciech Ślawski

Add indexes on es.id, eus.eb_evaluation_symptom_id, es.is_active.

Indexes are already present.



5.8k
in reply to Paul Scarrone
edited Nov '19

have you pulled the raw query and run an explain on it?

This is RAQ SQL and Explain results

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;

https://imgbbb.com/image/LsoM8x



2.8k

Would you mind providing your table structure with indexes? Also what DB engine are you using?



2.8k
Accepted
answer
in reply to Iftikhar uddin
edited Nov '19

The issue is related to Using Temporary; Using Filesort; This means the dbms has to create a temp table because it couldn't apply the were prior to the join so it had to apply the filter after the data was joined in a temp tablel That is alwso why your index isn't being used.

My dirty rewrite would be to try to apply the filter for is_active before joining

SELECT es_filter.id, es_filter.title, count(eus.eb_evaluation_symptom_id)
FROM (
    SELECT es.* 
    FROM eb_evaluation_symptom AS es 
    WHERE es.is_active = 1
) as es_filter
LEFT JOIN eb_evaluation_user_symptom AS eus ON es_filter.id = eus.eb_evaluation_symptom_id
GROUP BY eus.eb_evaluation_symptom_id;

My other advice would be if this data changes somewhat infrequently you may consider creating a materialized view for the whole query as this basically creates a concrete table from the results and you can then apply whatever future calls against it without the join cost. So on update or on an interval you can refresh this view. Think of it as a kind of query cache.

have you pulled the raw query and run an explain on it?

This is RAQ SQL and Explain results

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;

https://imgbbb.com/image/LsoM8x