Remove subquery to improve performance

Question

The following query is incredibly slow, most likely down to the subquery

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 has around 2500 records so this should not be taking that long. What can I use instead of the subquery?

EXPLAIN gives

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

Tables DESC below

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    

releases

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

Your subquery joins to release_artists but your outer query does as well which is unusual. It looks like you want the 60 most recents releases with the release artists where the charts extended arist is Quickinho. If I am understanding the query correctly I don't think you need a subquery at all.

I would try

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

You haven't given much info about the charts extended table but if you are getting duplicates because it has n rows per release artist with the above you can just adjust your group by or use distinct etc. to remove the duplicates.