0

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

I was wondering if it was possible to re write 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) are then published. The slide and the version that is published is set in the pp_published_slides tables. And the overall publish 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.

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

Any help would be greatly appreciated, kinda at the limits of my SQL knowledge....

Matt Bryson
  • 2,286
  • 2
  • 22
  • 42
  • Matt please update what you want to achieve i.e. what result you want from these queries. So that there can be a different way altogether. – Akhil Oct 09 '14 at 12:20
  • Do not use "SELECT *". Name and properly qualify each of the columns that you actually want returned. – Strawberry Oct 09 '14 at 12:26
  • 1
    Beyond that, these do seem to be two different queries. Are you sure it's legitimate to combine them as one? If so, then consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Oct 09 '14 at 12:33
  • I don't use SELECT *, this is a simplified version for the purposes of the post - ill update in a bit with some more info. – Matt Bryson Oct 09 '14 at 15:03
  • have updated with a sqlfiddle and some notes – Matt Bryson Oct 09 '14 at 16:06

1 Answers1

2

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';
Strawberry
  • 33,750
  • 13
  • 40
  • 57