Why is this simple nested select so slow?

[apauto]

Can take 20-30 seconds to execute:

SELECT DISTINCT id FROM features WHERE id IN
(SELECT DISTINCT id FROM features WHERE featureId = 10 AND active = 'y')
AND id IN
(SELECT DISTINCT id FROM features WHERE featureId = 12 AND active = 'y')

Any idea guys?

Thanks!

[apauto]

even one of them runs slow:

SELECT DISTINCT id FROM features WHERE id IN
(SELECT DISTINCT id FROM features WHERE featureId = 10 AND active = 'y')

thanks

[LifeinAsia]

Check your indexes.

[janharders]

yeah, you should probably have a combined index on featureId and active.
also: why don't you write that with joins?

[Demaestro]

Wait isn't that the same as

SELECT DISTINCT id FROM features WHERE featureId in (10,12) AND active = 'y'

[edited by: Demaestro at 7:43 pm (utc) on July 2, 2009]

[janharders]

Wait isn't that the same as

SELECT DISTINCT id FROM features WHERE featureId in (10,12) AND active = 'y'

no, that would be OR, he's looking for AND - it can't be done without a join or subselect imho.

[LifeinAsia]

SELECT DISTINCT id FROM features WHERE featureId in (10,12) AND active = 'y'

No, that's what I thought at first. But work out the logic. The OP is looking for IDs that have at least one row where the featureID=10 and at least one row where featureID=12 (and both rows have active='Y').
Think:
ID featureID active
1 10 'y'
1 12 'y'
2 10 'y'

The OP's query will only return ID 1, but the "simplified" query would return 1 & 2.

[Demaestro]

lia, jan you guys are right.... of course :)

[apauto]

SELECT DISTINCT id FROM features WHERE featureId in (10,12) AND active = 'y'

When I tried this last time, it returned an id that had at least one y in 10 or 12. Not for both.

I'll try it again though... thanks guys!

[FourDegreez]

How about joining the table with itself, like:

SELECT DISTINCT a.id FROM features a, features b
WHERE a.id=b.id AND
a.featureId = 10 AND a.active = 'y'
AND b.featureId = 12 AND b.active = 'y'

Haven't tested it, but worth a try.

[apauto]

FourDegreez, it seems that won't be very efficient.

Anyone have any other ideas? Thanks guys

[apauto]

FourDegreez, that works, and seems pretty quick with just two featureId's.

What would you do if I was searching on say... 20 featureIds?

Thanks in advance!