1

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?

Neil M.
  • 456
  • 5
  • 16
  • you can move one of the OR conditions to a 'second' query, and then join them back with a UNION... it would be unusual that the optimizer couldnt resolve that in the first place. – Randy Oct 26 '11 at 17:20
  • Probably worth a shot.. Maybe the stats are out of date, and the planner thinks the majority of the rows are either x or y, so doesn't bother to hit the index? I wander if an `EventID IN (...)` would work any better? – Mike Christensen Oct 26 '11 at 17:22
  • Aside from answers, what is the context of Table1 and Table2, their relationship, and what are you trying to get OUT of this query... Your overall COUNT() is just going to give a number without any context of which event or attendee. – DRapp Oct 26 '11 at 17:29

3 Answers3

1

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)

JML
  • 409
  • 2
  • 8
0
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
Randy
  • 16,480
  • 1
  • 37
  • 55
0

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 )
DRapp
  • 47,638
  • 12
  • 72
  • 142