mysqloptimizationsubquerymysql-dependent-subquery

MySQL - can I avoid these correlated / dependent subqueries?


I have been optimising a MySQL query, and it has 2 correlated / dependent subqueries.

How can I rewrite to avoid these?

SELECT *
FROM `pp_slides` 
JOIN `pp_slide_content` 
    ON `pp_slides`.`id` = `pp_slide_content`.`slide_id` 
    AND `pp_slide_content`.`version` = (
        SELECT max(`version`)
        FROM `pp_slide_content`
        WHERE `slide_id` = `pp_slides`.`id`
    )
LEFT JOIN `pp_published_slides` 
    ON `pp_published_slides`.`slide_id` = `pp_slides`.`id` 
    AND `pp_published_slides`.`slide_version` = `pp_slide_content`.`version` 
    AND `pp_published_slides`.`publish_id` = (
        SELECT max(`publish_id`)
        FROM `pp_published_slides`
        WHERE `pp_published_slides`.`slide_id` = `pp_slides`.`id`
        AND `pp_published_slides`.`slide_version` = `pp_slide_content`.`version`
    )
LEFT JOIN `pp_publish`
    ON `pp_publish`.`id` = `publish_id`
WHERE `pp_slides`.`product_id` =  '2'
AND `pp_slides`.`country_code` =  'gb'

A quick overview: A slide is created, and supports versioned changes. A slide (and other entities) is then published. The slide and the version that is published is set in the pp_published_slides tables. And the overall published object is saved in pp_publish.

The above SQL will load up a slide object and include extra data about the latest version, when it was published, etc.

http://sqlfiddle.com/#!2/902fb4/1


Solution

  • Here's an example showing part of your query rewritten without the correlated subquery...

    SELECT s.*
         , c.*
      FROM slides s  
      JOIN slide_content c
        ON c.slide_id = s.id
      JOIN ( SELECT slide_id, MAX(version) max_version FROM slide_content GROUP BY slide_id ) x
        ON x.slide_id = c.slide_id
       AND x.max_version = c.version 
     WHERE s.product_id = 2
       AND s.country_code = 'gb';