如何将依赖子查询转换为联接以获得更好的性能? [英] How to convert dependent subquery to join for better performance?

问题描述

我有一个存储主题"的数据库，每个主题都与一堆图像(=这些主题的屏幕快照)相关联.现在，我想显示最新的10个主题，对于每个主题，我只想从数据库中获取一张图像(ID最低的图像).

I have a database that stores "themes" and every theme is associated with a whole bunch of images (=screenshots of these themes). Now I want to display the latest 10 themes and for every theme I only want to get one single image from the database (the one with the lowest ID).

当前我的查询如下(我正在使用子查询):

Currently my query looks like this (I am using a subquery):

SELECT DISTINCT 
  t.theme_id, t.theme_name, theme_date_last_modification, image_id, image_type

FROM 
  themes t, theme_images i

WHERE 
  i.theme_id = t.theme_id
  AND t.theme_status = 3
  AND t.theme_date_added < now( )
  AND i.image_id = (
    SELECT MIN( image_id )
    FROM theme_images ii
    WHERE ii.theme_id = t.theme_id 
  )

GROUP BY 
  t.theme_id

ORDER BY 
  t.theme_date_last_modification DESC

LIMIT 10

它可以工作，但是查询速度很慢.当我使用EXPLAIN时，我可以看到有一个依赖子查询".是否可以将此依赖子查询转换为某种可以通过mysql更快处理的联接?

It works, but the query is very slow. When I use EXPLAIN I can see that there's a "dependent subquery". Is it possible to convert this dependent subquery into some kind of join that can be processed faster by mysql?

P.S .:我的实际查询要复杂得多，并且要使用更多的表.我已经尝试过尽可能简化它，以便您可以专注于性能问题的实际原因.

P.S.: My actual query is much more complex and makes use of more tables. I have already tried to simplify it as much as possible so that you can concentrate on the actual reason for the performance-problems.

这是EXPLAIN的输出:

This is the output of EXPLAIN:

id  select_type         table   type    possible_keys              key       key_len   ref                 rows  Extra   
1   PRIMARY             t       index   PRIMARY,themes             themes    212       NULL                5846  Using where; Using index; Using temporary; Using filesort
1   PRIMARY             i       eq_ref  PRIMARY,theme_id,image_id  PRIMARY   4         func                1     Using where
2   DEPENDENT SUBQUERY  ii      ref     theme_id                   theme_id  4         themes.t.theme_id   6   

推荐答案

首先尝试此查询-

SELECT
  t.*, ti1.*
FROM
  themes t
JOIN theme_images ti1
  ON ti1.theme_id = t.theme_id
JOIN (SELECT theme_id, MIN(image_id) image_id FROM theme_images GROUP BY theme_id) ti2
  ON ti1.theme_id = ti2.theme_id AND ti1.image_id = ti2.image_id
ORDER BY 
  t.theme_date_last_modification DESC
LIMIT 10

另一个解决方案-

SELECT
  t.*, ti.*
FROM
  themes t
JOIN (SELECT * FROM theme_images ORDER BY image_id) ti
  ON ti.theme_id = t.theme_id
GROUP BY
  theme_id 
ORDER BY 
  t.theme_date_last_modification DESC
LIMIT
  10

然后添加您的WHERE过滤器.

Then add your WHERE filter.

这篇关于如何将依赖子查询转换为联接以获得更好的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！