How to convert dependent subquery to join for better performance?

Question

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

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.: 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.

EDIT: 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

It would be better if you added the EXPLAIN plan and the output of `SHOW CREATE TABLE tablename;` for the 2 tables. — ypercubeᵀᴹ, Sep 12 '13 at 11:19

Devart · Accepted Answer · 2013-09-12T13:15:30.380

4

Try this query firstly -

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

One more solution -

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

Then add your WHERE filter.

edited Sep 12 '13 at 13:15

answered Sep 12 '13 at 11:18

Devart

119,203
23
166
186

This query needs exactly the same time than my original query. P.S.: I had to add ", theme_id" to the lower SELECT part to make your query work. – Keyser Soze Sep 12 '13 at 12:10
Yes, it should be there;-) – Devart Sep 12 '13 at 12:41
Try another solution, look at the second query. – Devart Sep 12 '13 at 13:15
I have to correct my own comment. I don't know what I did one hour ago - maybe I submitted the wrong query when measuring the execution time for your query because I had too many phpMyAdmin-query-windows open on my computer? But after checking your (first!) query again I can tell you that it is actually 3 times faster than my previous query. Still not lightning-fast, but at least the dependent subquery is gone according to EXPLAIN :-) – Keyser Soze Sep 12 '13 at 13:36
I've chosen your first query as best (=fastest) solution and it has already been implemented on my website. Thanks a lot for your help! – Keyser Soze Sep 12 '13 at 14:10

ypercubeᵀᴹ · Answer 2 · 2013-09-12T12:51:06.473

1

One approach is to first LIMIT on the themes table, then JOIN to images:

SELECT
    t.theme_id, t.theme_name, t.theme_date_last_modification, 
    ti.image_id, ti.image_type
FROM
      ( SELECT theme_id, theme_name, theme_date_last_modification
        FROM themes t
        WHERE theme_status = 3
          AND theme_date_added < now( )
        ORDER BY 
          theme_date_last_modification DESC
        LIMIT 10 
      ) AS t
    JOIN                     -- LEFT JOIN if you want themes without an image 
        theme_images AS ti   -- to be shown
          ON ti.theme_id = t.theme_id
         AND ti.image_id =
             ( SELECT ii.image_id
               FROM theme_images AS ii
               WHERE ii.theme_id = t.theme_id
               ORDER BY ii.image_id
               LIMIT 1
             ) 

ORDER BY 
    t.theme_date_last_modification DESC ;

With an index on themes (theme_status, theme_date_last_modification, theme_id, theme_date_added) the limit subquery should be efficient.

I suppose you also have a (unique) index on theme_images (theme_id, image_id).

edited Sep 12 '13 at 12:51

answered Sep 12 '13 at 11:14

ypercubeᵀᴹ

113,259
19
174
235

After changing a few typos in your query ("images" -> "theme_images", "images_id" -> "theme_images"), I can tell you that your query is 3 times slower than my original query (even after making sure that the indexes you mentioned are set). – Keyser Soze Sep 12 '13 at 12:24
Curious. Is it slow if you remove the `AND theme_date_added < now( )` condition? – ypercubeᵀᴹ Sep 12 '13 at 12:30
@KeyserSoze Oh my bad, sorry. Correcting the query now. – ypercubeᵀᴹ Sep 12 '13 at 12:33
It's still slow after removing `AND theme_date_added < now( )` – Keyser Soze Sep 12 '13 at 12:36
Corrected. I had a condition missing (`ON ti.theme_id = t.theme_id`) – ypercubeᵀᴹ Sep 12 '13 at 13:01
The other solution by user Devart is working faster and I have implemented it now. But I also wanted to thank you for your help. – Keyser Soze Sep 12 '13 at 14:12

How to convert dependent subquery to join for better performance?

2 Answers2

Linked