CODEWITHSUNDEEP
mysqlquery-optimizationsubquery
user963395
user963395

Reputation:

Improve horrible, multiple-sub-query SQL

How would you improve the following product i18n retrieval SQL to NOT use sub-queries?

SELECT
 l.code,
 l.rtl,
 l.name,
 (SELECT title FROM product_i18n WHERE product_id = :product_id AND culture = l.code) AS title,
 (SELECT description FROM product_i18n WHERE product_id = :product_id AND culture = l.code) AS description,
 (SELECT keywords FROM product_i18n WHERE product_id = :product_id AND culture = l.code) AS keywords
FROM
 language AS l
WHERE
 l.status = 1
ORDER BY
 IFNULL(l.sort, l.id)

Upvotes: 0

Views: 113

Answers (2)

Mike Christensen
Mike Christensen

Reputation: 91666

Try

SELECT
 l.code,
 l.rtl,
 l.name,
 p.title,
 p.description,
 p.keywords
FROM
 language AS l
INNER JOIN product_i18n p ON (p.product_id = :product_id AND p.culture = l.code)
WHERE
 l.status = 1
ORDER BY
 IFNULL(l.sort, l.id)

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

something like

SELECT
 l.code,
 l.rtl,
 l.name,
 p.title,
 p.description,
 p.keywords
From
 language AS l
inner join product_il8n p on p.culture = l.code and p.product_id = :product_id
WHERE
 l.status = 1
ORDER BY
 IFNULL(l.sort, l.id)

Upvotes: 2

Related Questions