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

It hangs for how long? A few seconds, a couple of minutes, forever? — Marcelo Cantos, Jul 26 '10 at 12:23
put index on `id` of table `person` and `user_id` of table `participation` — Salil, Jul 26 '10 at 12:26
@Zaki, IN is slow when compared to a proper join is possible on the same query. However, I wouldn't use the blanket statement that it's slow. It's great to use when you want records for a constantly defined list of ids. — Mike Sherov, Jul 26 '10 at 12:35

oezi · Answer 1 · 2010-07-26T13:35:33.433

2

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'

edited Jul 26 '10 at 13:35

answered Jul 26 '10 at 12:25

oezi

51,017
10
98
115

1

Subquery is executed for each row of the main query, so JOIN is a proper and much faster approach. – Naktibalda Jul 26 '10 at 13:10
@Naktibalda, are you sure? That wasn't my understanding of how a subquery worked. I am almost positive that a temporary table is created so it's only executed once. – Mike Sherov Jul 26 '10 at 13:15
Thanks oezi, I'll give it a go if/when the server comes up again haha. – Chris Jul 26 '10 at 13:24
Do not use or recommend the comma syntax. Use the proper ANSI INNER JOIN format. – Thomas Jul 26 '10 at 13:27

score 1 · Answer 2 · answered Jul 26 '10 at 14:08

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.

MySQL Subquery Causing Server to Hang

2 Answers2