mysql - 删除子查询以提高性能

Question

下面的查询非常慢，很可能是因为子查询

SELECT * 
FROM releases
INNER JOIN release_artists ON release_artists.release_id = releases.id
WHERE release_artists.artists IN (SELECT release_artists.artists
                                  FROM release_artists
                                  INNER JOIN charts_extended ON charts_extended.release_id = release_artists.release_id
                                  WHERE charts_extended.artist = 'Quickinho'
                                  GROUP BY release_artists.artists)
GROUP BY releases.id
ORDER BY releases.date DESC
LIMIT 0,60

charts_extended.artist 有大约 2500 条记录，所以这应该不会花那么长时间。我可以使用什么来代替子查询？

EXPLAIN 给出

1   PRIMARY releases    index   PRIMARY date    82  NULL    60  Using temporary
1   PRIMARY release_artists ref release_id  release_id  4   soundshe.releases.id    1   Using where; Using index
2   DEPENDENT SUBQUERY  charts_extended ref artist_2,release_id,artist  artist_2    82  const   2472    Using where; Using index; Using temporary; Using filesort
2   DEPENDENT SUBQUERY  release_artists ref release_id  release_id  4   soundshe.charts_extended.release_id 1   Using index

下面的表 DESC

charts_extended

id  int(11) NO  PRI NULL    auto_increment
artist  varchar(80) NO  MUL NULL    
url text    NO      NULL    
release_id  int(11) NO  MUL NULL    
date    varchar(50) NO      NULL    
type    varchar(4)  NO      NULL    
source  varchar(3)  NO      NULL    

发布

id  int(11) NO  PRI NULL    
artist  varchar(255)    NO  MUL NULL    
all_artists varchar(200)    NO  MUL NULL    
format  varchar(80) NO  MUL NULL    
title   varchar(255)    NO  MUL NULL    
label   varchar(255)    NO  MUL NULL    
label_no_country    varchar(255)    NO  MUL NULL    
link    text    NO      NULL    
genre   varchar(50) NO  MUL NULL    
date    varchar(80) NO  MUL NULL    
image   text    NO      NULL    
favourite   varchar(1)  NO      NULL    
time    varchar(20) NO      NULL    
category    varchar(30) NO  MUL NULL    
format_category varchar(20) NO  MUL NULL    
display varchar(1)  NO      NULL    
image_stored    varchar(1)  NO      NULL

release_artists

id  int(11) NO  PRI NULL    auto_increment
release_id  int(10) NO  MUL NULL    
artists varchar(100)    NO      NULL    

Answer 1

您的子查询连接到 release_artists，但您的外部查询也连接到 release_artists，这是不寻常的。看起来你想要 60 个最近发布的发行艺术家，其中图表扩展艺术家是 Quickinho。如果我对查询的理解正确，我认为您根本不需要子查询。

我会努力

SELECT * FROM releases
INNER JOIN release_artists
ON release_artists.release_id=releases.id
INNER JOIN charts_extended
ON charts_extended.release_id=release_artists.release_id
WHERE charts_extended.artist='Quickinho'
GROUP BY releases.id
ORDER BY releases.date DESC
LIMIT 0,60

您没有提供有关图表扩展表的太多信息，但如果您得到重复项，因为每个发行艺术家都有 n 行，您可以调整您的分组依据或使用 distinct 等来删除重复项。