CODEWITHSUNDEEP
mysql
Neil M.
Neil M.

Reputation: 456

SQL "OR" in the where clause causing no index to be used

I've got a query as follows:

SELECT COUNT(Table1.Identifier) AS NonCancelCnt 
FROM Table1, Table2 
LEFT JOIN eventattendees ON eventattendees.AttendeeID = 47322 
LEFT JOIN eventsignup ON eventattendees.AttendeeID = eventsignup.AttendeeID 
LEFT JOIN transactions on transactions.registrationID=eventsignup.regid 
WHERE ((eventsignup.EventID = Table1.Identifier) Or (eventsignup.EventID = Table1.AttendanceLinkID))

The "OR" clause is causing no index to be used. If I remove either portion, my execution path goes from 95,000 to 200, and speed is drastically increased.

I'm not very experienced in reworking such a thing, what is my best option for doing so?

Upvotes: 1

Views: 174

Answers (3)

DRapp
DRapp

Reputation: 48179

Not understanding what Table1 and Table2 are, nor are they joined in any shape, you will get a Cartesian result (for each record in Table1, will be joined with each record in Table2)

Additionally, your where clause could just be simplified with an IN clause

where
   eventsignup.EventID IN ( Table1.Identifier, Table1.AttendanceLinkID )

Upvotes: 0

JML
JML

Reputation: 409

First, you should rewrite your query to specify how Table1, Table2 and eventattendees are joined. Also choose whether you want to specify the columns to use to join in the WHERE clause or after the JOIN keyword. After you clean it up a bit, the optimizer may do a better job of picking the proper index to use.

If that still doesn't work, you can use a SQL hint to specify the index you want the optimizer to use:

WITH INDEX(IX_nameofindex)

Upvotes: 1

Randy
Randy

Reputation: 16673

SELECT COUNT(Table1.Identifier) AS NonCancelCnt 
FROM Table1, Table2 
LEFT JOIN eventattendees ON eventattendees.AttendeeID = 47322 
LEFT JOIN eventsignup ON eventattendees.AttendeeID = eventsignup.AttendeeID 
LEFT JOIN transactions on transactions.registrationID=eventsignup.regid 
WHERE eventsignup.EventID = Table1.AttendanceLinkID

union all

SELECT COUNT(Table1.Identifier) AS NonCancelCnt 
FROM Table1, Table2 
LEFT JOIN eventattendees ON eventattendees.AttendeeID = 47322 
LEFT JOIN eventsignup ON eventattendees.AttendeeID = eventsignup.AttendeeID 
LEFT JOIN transactions on transactions.registrationID=eventsignup.regid 
WHERE eventsignup.EventID = Table1.Identifier

Upvotes: 0

Related Questions