Do you set an index on JOIN clauses or where clauses, or both?

Question

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?

  t2.id t2.userid

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)

Answer 1

I would recommend to always put a non-clustered index on the columns that will be used in JOIN conditions - the foreign key columns. This helps in several ways - JOIN operations will be faster, and enforcing the FK constraint (checking whether there's a child row attached when attempting to delete the parent row) will also benefit from those indices.

Then check to see how your system performs. If it performs below your expectations - carefully add one index and see if the overall system performance improves. If not: remove the index again. Repeat over until you're happy with the performance. Columns used in WHERE or ORDER BY clauses are the prime candidates for those indices - but don't over-index! That's even worse than having no indices at all.

See Kimberly The Queen of Indexing Tripp's excellent blog post - Indexes: just because can, doesn't mean you should! on that very topic.