CODEWITHSUNDEEP
mysql
deerua
deerua

Reputation: 408

MySQL: How-to optimize query with two identical subqueries?

I have query that work fine, but it has 2 the same subqueries

SELECT *, 
    (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) AS poffers 
FROM `product` P 
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14)) 
WHERE P.deleted is NULL 
    AND (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) > 0

I try to optimize with JOIN, but new query returns empty result

SELECT *, OJ.pcount AS poffers
FROM `product` P 
JOIN product_section PS ON PS.product_id = P.id AND PS.section_id IN (14)
JOIN (SELECT count(O.id) AS pcount, O.product_id FROM `offer` O ) 
    AS OJ ON OJ.product_id = P.id
WHERE P.deleted is NULL AND OJ.pcount > 0

Than i try variables, but i think i use it wrong, query returns empty result

SELECT *,  
    @o := (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) 
    AS poffers 
FROM `product` P 
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14)) 
WHERE P.deleted is NULL 
    AND @o > 0

Upvotes: 0

Views: 122

Answers (2)

MvG
MvG

Reputation: 60858

Avoid the dependent subqueries. Instead, have one query which relates products to offer counts. This query you can use in several places. It will most likely be stored temporarily in memory, avoiding duplicate computations, but the database engine might do more clever things as well.

SELECT *, O.cnt AS poffers
FROM product P
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14))
JOIN (SELECT product_id, count(id) AS cnt
      FROM offer
      GROUP BY product_id
     ) O ON (O.product_id = P.id)
WHERE P.deleted is NULL AND O.cnt > 0

If the relation between P and PS is one-to-one, then you could even work without any subquerys whatsoever:

SELECT *, COUNT(O.id) AS poffers
FROM product P
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14))
JOIN offer O ON (O.product_id = P.id)
WHERE P.deleted is NULL
GROUP BY P.id

You also should consider whether there is a good reason to count a specific column. In most cases, COUNT(*) will be faster than COUNT(col). The only reason to use the latter is if you explicitely want to exclude NULL values from the count.

Upvotes: 1

xkeshav
xkeshav

Reputation: 54016

give it a try ( not tested )

SELECT P.*,PS.*,count(O.id) AS oCount
FROM `product` P
INNER JOIN `product_section` PS ON (PS.product_id = P.id)
INNER JOIN `offer` O ON (O.product_id = P.id) 
WHERE P.deleted is NULL AND PS.section_id =14
GROUP BY O.product_id
HAVING oCount> 0

more optimized

SELECT P.*,PS.*,count(O.id) AS oCount
FROM `product` P
INNER JOIN (`product_section` PS,`offer` O) ON 
(PS.product_id = P.id AND O.product_id = P.id) 
WHERE P.deleted is NULL AND PS.section_id =14
GROUP BY O.product_id
HAVING oCount> 0

TIPS:

  • dont use *, instead write only those column names which you want to retrieve
  • make index on all columns which are using with ON & WHERE condition

Reference

Upvotes: 1

Related Questions