StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL groupby with sum
text
Body
copied!<p>I have a query with group by and sum. I have close to 1 million records. When i run the query it is taking 2.5s. If i remove the group by clause it is taking 0.89s. Is there any way we can optimize the query using group by and sum together.</p> <pre><code> SELECT aggEI.ei_uuid AS uuid,aggEI.companydm_id AS companyId,aggEI.rating AS rating,aggEI.ei_name AS name, compdm.company_name AS companyName,sum(aggEI.count) AS activity FROM AGG_EXTERNALINDIVIDUAL AS aggEI JOIN COMPANYDM AS compdm ON aggEI.companydm_id = compdm.companydm_id WHERE aggEI.ei_uuid is not null and aggEI.companydm_id IN (8) and aggEI.datedm_id = 20130506 AND aggEI.topicgroupdm_id IN (1,2,3,4,5,6,7) AND aggEI.rating >= 0 AND aggEI.rating <= 100 GROUP BY aggEI.ei_uuid,aggEI.companydm_id LIMIT 0,200000 </code></pre> <p>Explain result is as below:</p> <pre><code> 1 SIMPLE compdm const PRIMARY,companydm_id_UNIQUE,comp_idx PRIMARY 8 const 1 Using temporary; Using filesort 1 SIMPLE aggEI ref PRIMARY,datedm_id_UNIQUE,agg_ei_comdm_fk_idx,agg_ei_datedm_fk_idx,agg_ei_topgrp_fk_idx,uid_comp_ei_dt_idx,uid_comp_dt_idx,comp_idx datedm_id_UNIQUE 4 const 197865 Using where </code></pre> <p>Also i didn't understand why compdm table is executed first. Can someone explain?</p> <p>I have index on AGG_EXTERNALINDIVIDUAL table with combination of ei_uuid,companydm_id,datedm_id. The same is shown on aggEI table under possible keys as uid_comp_dt_idx. But aggEI table is taking datedmid_UNIQUE as key. I didn't understand the behavior.</p> <p>Can someone explain?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload