MySQL-我可以避免这些相关/相关子查询吗? [英] MySQL - can I avoid these correlated / dependant subqueries?

问题描述

我有一个我一直在优化的MySQL查询，目前它有2个相关的/相关的子查询.

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'

快速概述: 将创建一张幻灯片，并支持版本更改. 然后发布幻灯片(和其他实体). 幻灯片和发布的版本在pp_published_slides表中设置. 并将整个发布对象保存在pp_publish中.

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.

上面的SQL将加载幻灯片对象，并包含有关最新版本，发布时间等的额外数据.

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

这是一个sqlfiddle http://sqlfiddle.com/#!2/902fb4/1

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

在我的SQL知识的极限范围内，任何帮助将不胜感激.

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

推荐答案

下面是一个示例，显示部分查询被重写而没有相关的子查询...

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';

这篇关于MySQL-我可以避免这些相关/相关子查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！