Trivial query takes over 10 seconds

[Jonas Jensen]

I have a performance problem that I've isolated down to the minimal query that triggers it:

SELECT device.* FROM device WHERE device.device_id IN (SELECT device_ip.device_id FROM device_ip GROUP BY device_ip.device_id HAVING COUNT(device_ip.device_id) > 1);

The purpose of the query is to find devices with more than one IP.

This query takes over 10 seconds even though the device and device_ip tables both have less than 5000 rows. There are indexes for everything, but that's beside the point, as even a full table scan should be no problem at all with so few rows.

EXPLAIN gives me:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: device
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4195
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: device_ip
type: index
possible_keys: NULL
key: device_id
key_len: 4
ref: NULL
rows: 4198
Extra: Using index
2 rows in set (0.00 sec)

I think the problem is that it's classifying the subquery as "DEPENDENT SUBQUERY", although it's clearly independent and should be executed only once. When executed on its own, it returns 3 rows in 0.01 sec. Replacing the subquery with "IN (654, 655, 656)" makes the outer query compete in 0.01 seconds too.

Is this a MySQL optimizer bug, or am I doing something wrong? I've tried versions 5.0.4-0.beta.1mdk and 4.1.5-0.1mdk from Mandrake.

For this test case, the following equivalent query does the job in 0.01 seconds, although it's forced to do a full table scan and thousands of index lookups that the previous one shouldn't if it were properly optimized:

SELECT device.* FROM device WHERE EXISTS (SELECT NULL FROM device_ip ip1, device_ip ip2 WHERE ip1.device_id=device.device_id AND ip1.device_id=ip2.device_id AND ip1.ip<>ip2.ip)