What is wrong with this query (select ... where in (select ...)?

Question

iplist table (id, username, ip, created) - stores the distinct IP addresses of each user

I want to find all the usernames who ever had the same IP address as 'angryuser'

MySQL QUERY:

    SELECT  username,
        ip  
FROM    `iplist` 
WHERE   ip  IN (
                    SELECT  ip 
                    FROM    iplist 
                    WHERE   username='angryuser'
                )

If I run this query it gets in a loop and kills my server :)

Why?

Update:

Here's the execution plan:

id  select_type         table   type  possible_keys  key   key_len  ref   rows    Extra
1   PRIMARY             iplist  ALL   NULL           NULL  NULL     NULL  102757  Using where
2   DEPENDENT SUBQUERY  iplist  ALL   NULL           NULL  NULL     NULL  102757  Using where

Answer 1

Well, the query should run fine. There's most likely another problem here. Do you have any indexes on your table, and how large IS your table?

Answer 2

You need to create a composite index on (ip, username)

MySQL tries to optimize your query into this:

SELECT  ip, username
FROM    iplist ui
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    iplist uo
        WHERE   uo.username = 'angry'
                AND uo.id = ui.id
        )

The inner EXISTS (that needs to be evaluated for each row from users) will require but a single index scan if you create this index.

However, it is better to rewrite the query like that:

SELECT  io.username, io.ip
FROM    iplist ia
JOIN    iplist io
ON      io.ip = ia.ip
WHERE   ia.username = 'angryuser'
        AND io.username <> 'angryuser'

This way, the 'angryuser' query will be made leading and the engine will need to check only several IPs.

The index on (ip, username) you should create anyway for this to work fast.

Update:

If your usernames are too long, you need to create a prefixed index:

CREATE INDEX ix_iplist_ip_username ON (ip, username (30))

30 characters is enough for this index to be selective.

Answer 3

Why shouldn't you use:

SELECT username, ip FROM iplist WHERE username='angryuser'

It does the same

Answer 4

Have you tried a normal INNER JOIN

SELECT  p.username,
        p.ip
FROM    iplist p INNER JOIN
        iplist s ON p.ip = s.ip
WHERE   s.username='angryuser'

Ensure that you have the correct indexes on the table as previously mentioned.

Answer 5

For a bit of a backgrounder on why the original subquery performs slower then the Join, see: http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

Answer 6

Probably because the sub-query is returning a large resultset:

SELECT  ip 
FROM    iplist 
WHERE   username='angryuser'