Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Say my query looks like:

SELECT t1, t2
  LEFT JOIN t2 ON ( = 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.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)

share|improve this question

migrated from Jul 12 '12 at 17:10

This question came from our site for professional and enthusiast programmers.

I would say both but note that updates and inserts will be slower the more indexes you add. – Steve Wellens Jul 11 '12 at 15:57
up vote 2 down vote accepted

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.

share|improve this answer
Her blog posts about indexing is a exceptional reading... – Fabricio Araujo Jul 12 '12 at 17:53
I'm not sure if trial and error is a good approach? – Paul Wasilewski Jun 1 at 4:04
@PaulWasilewski: if you have a better, more efficient way - please do share it with us! – marc_s Jun 1 at 12:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.