MySQL Subquery Causing Server to Hang

Question

I'm trying to execute the following query

SELECT * FROM person 
         WHERE id IN 
             ( SELECT user_id FROM participation 
                    WHERE activity_id = '1' AND application_id = '1' 
             )

The outer query returns about 4000 responses whilst the inner returns 29. When executed on my web server nothing happened and when I tested it locally mysql ended up using 100% CPU and still achieved nothing. Could the size be the cause?

Specifically it causes the server to hang forever, I'm fairly sure the web server I ran the query on is in the process of crashing due to it (woops).

Answer 1

why don't you use an inner join for this query? i think that would be faster (and easier to read) - and maybe it solves your problem (but i can't find a failure in your query).

EDIT: the inner-join-solution would look like this:

SELECT
  person.*
FROM
  person
INNER JOIN
  participation
ON
  person.id = participation.user_id
WHERE
  participation.activity_id = '1'
AND
  participation.application_id = '1'

Answer 2

How many rows are there in participation table and what indexes are there? A multi-column index on (user_id, activity_id, application_id) could help here.

Re comments: IN isn't slow. Subqueries within IN can be slow, if they're correlated to outer query.