Using "IN" in the WHERE clause

[allen.brost]

Take a look at this query. The key part is this...

AND cha.unitid = 1

If I change the line to...

AND cha.unitid IN (1)

The query goes for taking a few seconds to infinity....

In Oracle this worked fine. How is MySQL using the "IN" statement and indexes differently?


SELECT SUM(bla), substring(cha.day,1,10) bhour, cha_ntid.logicalName CHA, cel_ntid.logicalName CEL, sit_ntid.logicalName SIT, bsc_ntid.logicalName BSC, omc_ntid.logicalName OMC, omc_ntid.marketCode MARKET
FROM cha, cha_ntid, cel_ntid, sit_ntid, bsc_ntid, omc_ntid
WHERE cha.marketCode = 'NY'
AND cha.day >= '2004-05-27 17:00' AND cha.day < '2004-05-27 18:00'
AND DAYOFWEEK(cha.day) != 1
AND DAYOFWEEK(cha.day) != 7
AND cha.unitid = 1
AND bsc_ntid.logicalName = 'EBSC_04_NY_01_M3_S'
AND sit_ntid.logicalName IN ('NNY0969R_ISMillerPl')
AND cha.marketCode = cha_ntid.marketCode
AND cha.networkID = cha_ntid.networkID
AND cha.networkID_CEL = cha_ntid.parentID
AND cha.unitid = cha_ntid.unitID
AND cha_ntid.parentID = cel_ntid.networkID
AND cha_ntid.marketCode = cel_ntid.marketCode
AND cha_ntid.unitid = cel_ntid.unitid
AND cel_ntid.parentID = sit_ntid.networkID
AND cel_ntid.marketCode = sit_ntid.marketCode
AND cel_ntid.unitid = sit_ntid.unitid
AND sit_ntid.parentID = bsc_ntid.networkID
AND sit_ntid.marketCode = bsc_ntid.marketCode
AND sit_ntid.unitid = bsc_ntid.unitid
AND bsc_ntid.marketCode = omc_ntid.marketCode
AND bsc_ntid.unitid = omc_ntid.unitid
GROUP BY bhour, omc_ntid.marketCode, omc_ntid.logicalName, bsc_ntid.logicalName, sit_ntid.logicalName, cel_ntid.logicalName, cha_ntid.logicalName) a, (