CODEWITHSUNDEEP
mysql
dennis.s
dennis.s

Reputation: 73

mysql "in" clause quite slow(seems to hang)

I'm using mysql and a simple query like this seems to hang forever with cpu 100%.

  select login_name, server, recharge 
  from role_info 
  where login_name in (select login_name 
                       from role_info
                       group by login_name 
                       having count(login_name) > 1)

the table role_info is small, with only 33535 rows.

below is the output of explain:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | PRIMARY | role_info | ALL | NULL | NULL | NULL | NULL | 33535 | Using where |

| 2 | DEPENDENT SUBQUERY | role_info | index | NULL | a | 302 | NULL | 1 | Using index |

show processlist reports the query keeps Sending data.

Query | 1135 | Sending data | select login_name, server, recharge from role_info where login_name in (select login_name from ...

I can switch to join instead of "in" and it works fine. but still curious why this simple query should act so abnormally.

Upvotes: 0

Views: 276

Answers (2)

saaj
saaj

Reputation: 25293

I wonder how many people stumble over this bug again and again. It's long lasting MySQL bug#32665, where MySQL 6 is the target version. It means that MySQL will turn you uncorrelated subquery into correlated (executed per row of outer resultset). Just in case, in this blog post are some ideas how to workaround the limitation when you can't rewrite the query into using JOINs.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In some versions of MySQL, in with a subquery is quite inefficient. The subquery gets executed for each row being processed. In other words, the results are not cached. I think this is fixed in the most recent versions.

You know how to fix it, by using a join. In general, I gravitate to joins or exists in MySQL instead of using in with a subquery.

Upvotes: 0

Related Questions