MySQL Forums
Forum List  »  Oracle

Using "IN" in the WHERE clause
Posted by: allen.brost
Date: July 29, 2004 12:52PM

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, (

Options: ReplyQuote


Subject
Views
Written By
Posted
Using "IN" in the WHERE clause
4369
July 29, 2004 12:52PM
2664
July 31, 2004 05:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.