Why is MySQL hanging on this simple subquery?

Question

I have this simple query which runs in a about 5 seconds and return about 500 records, but then why I try to use it in a compound statement MySQL just hangs

SELECT DISTINCT ARTIST_ID FROM WORK
    GROUP BY ARTIST_ID
    HAVING AVG(WORK_MILLIS_VIEWED) > 10

The query below however never terminates. According to the process list it's just creating a temporary table, even though it should run only slightly longer than the sub-query, because the artist table is trivially small.

SELECT ARTIST_NAME FROM ARTIST
    WHERE ARTIST_ID IN (SELECT DISTINCT ARTIST_ID
         FROM WORK GROUP BY ARTIST_ID
         HAVING AVG(WORK_MILLIS_VIEWED) > 10)

Am I making a stupid mistake? The database doesn't seem to be doing anything else.

Answer 1

MySQL has a hard time with subqueries in the WHERE clause. It usually decides to run the subquery many times (once for each distinct ARTIST_ID value you compare to the subquery) even though you know and I know that the subquery won't change.

A workaround for this is to run the subquery in the FROM clause and JOIN to it:

SELECT A.ARTIST_NAME 
FROM (
    SELECT ARTIST_ID FROM WORK 
    GROUP BY ARTIST_ID HAVING AVG(WORK_MILLIS_VIEWED) > 10
) AS T
JOIN ARTIST A ON A.ARTIST_ID = T.ARTIST_ID

This will at least run the subquery just once, and store it in a temporary table while it joins to the other table instance.

You will also benefit from an index on the pair of columns (ARTIST_ID, WORK_MILLIS_VIEWED) in that order.