Say my query looks like:
SELECT t1, t2 FROM t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid) WHERE t1.enabled = 1 AND t1.startDate <= ??? AND (t1.counter = -1 OR t2.counter IS NULL OR (t1.counter > t2.counter)
Now this table might have a few hundred thousand rows in it.
Would you suggest I put an index on the JOIN clause only like this?
What about the where clause? Or is the join clause more important?
I realize testing is important, but in theory what should be done?
(This is for SQL Server 2000)