CODEWITHSUNDEEP
mysql
speedy
speedy

Reputation: 375

GROUP BY and sum() performance issue

I'm performing this query

SELECT sum(c.cantitate) AS num, 
produse.*,
cc.seo
FROM produse
LEFT JOIN comenzi c ON produse.id = c.produs
LEFT JOIN cosuri cs ON c.cos = cs.id
LEFT JOIN categorii cc ON produse.categorie = cc.id
WHERE cs.status = 'closed' AND produse.vizibil ='1'
GROUP BY produse.id
ORDER BY num DESC
LIMIT 14

Affected rows: 0 Found rows: 14 Warnings: 0 Duration for 1 query: 11.734 sec.
Without the GROUP BY and sum it takes 16ms.
This query is used for a list of top sale 14 products based on quantity sale.
How can i rewrite the query for better performance ?

Upvotes: 1

Views: 652

Answers (1)

xQbert
xQbert

Reputation: 35333

Maybe you need to generate the sum's before the joins...

Per Comment to note against original Question:

Do you want the sum of comenzi before the joins to cosuri and categorii which may have multiple records causing a potentially inflated sum or after the join with perhaps an inflated sum?

SELECT c.cantitate AS num, 
produse.*,
cc.seo
FROM produse
LEFT JOIN (Select produs, cos, sum(cantitate) as cantitate 
           FROM comenzi 
           GROUP BY produs, cos) c ON produse.id = c.produs
LEFT JOIN cosuri cs ON c.cos = cs.id
LEFT JOIN categorii cc ON produse.categorie = cc.id
WHERE cs.status = 'closed' AND produse.vizibil ='1'
ORDER BY num DESC
LIMIT 14

Example: If comenzi has a record with ID 1 and a cos of 'A' and a cantitate of 3 and joins to cosuri based on CS.ID which has ID 'A' listed 3 times then the sum for cantitate will be calculated 3+3+3 (9) instead of Just 3... which could be the problem. If we calculate the sum in advance, we avoid the problem (if it is one) and the overhead of summing that values perhaps thousands of times depending on the cardinality between cosuri and categorii to comenzi. Which... could improve performance.

To know for certain if this would improve performance we would actually need to

  1. Understand requirements of the results (Sum before join or after?)
  2. See some sample data along with statistics of each table to know the record counts
  3. Knowledge of indexes between table joins and any where clause criteria
  4. A view of the explain plan on the database.

Without these details we can't know for certain what would truly help. Everything is a guess without the facts; which there are few of here)

Upvotes: 2

Related Questions