CODEWITHSUNDEEP
mysqlsql
William
William

Reputation: 6546

Convert subquery to join query

this is the working MySQL query, is it possible to convert this into single flat join query to increase performance ? Rather than using sub-query below. Thanks

SELECT * from catAtt ca LEFT JOIN att a ON ca.attId = a.attId
WHERE ca.catId = 53 AND a.attCatId = 
(
SELECT DISTINCT eav.catId FROM ent LEFT JOIN eav ON ent.entId = eav.entId 
WHERE ent.key = 'somekey'
)

Upvotes: 0

Views: 483

Answers (3)

Pasetchnik
Pasetchnik

Reputation: 318

I ve tried three type of queries on sql server.

SELECT * from catAtt ca JOIN att a ON ca.attId = a.attId
WHERE ca.catId = 53 AND a.attCatId = 
(
SELECT DISTINCT eav.catId FROM ent LEFT JOIN eav ON ent.entId = eav.entId 
WHERE ent.key = 'somekey'
)

SELECT * from catAtt ca 
JOIN att a ON ca.attId = a.attId
JOIN 
(
  SELECT DISTINCT eav.catId FROM ent 
  LEFT JOIN eav ON ent.entId = eav.entId 
  WHERE ent.key = 'somekey'
)Z ON a.attCatId = Z.catId 
WHERE ca.catId = 53 

SELECT * from catAtt ca JOIN att a ON ca.attId = a.attId
WHERE ca.catId = 53 AND 
EXISTS 
   (
    SELECT 1 FROM ent LEFT JOIN eav ON ent.entId = eav.entId 
    WHERE ent.key = 'somekey' AND a.attCatId = eav.catId
   )

And the query cost for every query is the same 33%.

Guess the DataBase is smarter than we are.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would start by simplifying the query. Both your left joins are really inner joins because of the where clauses. Also, the select distinct should not be needed in the subquery.

select ca.*, att.*
from catAtt ca join
     att a 
     on ca.attId = a.attId 
where ca.catId = 53 and
      a.attCatId = (select eav.catId 
                    from ent join
                         eav 
                         on ent.entId = eav.entId 
                    where ent.key = 'somekey'
                   );

This suggests the use of indexes: catAtt(catId, attId), att(attId, attCatId), ent(key, entId), and eav(entId, catId).

You can also move the subquery in the where to the from. I don't think that will have much impact on performance. It is not correlated, so it is run once, and returns a single value.

Upvotes: 2

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT * from catAtt ca 
LEFT JOIN att a ON ca.attId = a.attId
INNER JOIN 
(
  SELECT DISTINCT eav.catId FROM ent 
  LEFT JOIN eav ON ent.entId = eav.entId 
  WHERE ent.key = 'somekey'
)Z ON a.attCatId = Z.catId 
WHERE ca.catId = 53

Try above code.

Hope this will help you.

Upvotes: 0

Related Questions