Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)

share|improve this question
    
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
add comment

migrated from stackoverflow.com Jul 12 '12 at 17:10

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

1 Answer

up vote 1 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
2  
Her blog posts about indexing is a exceptional reading... –  Fabricio Araujo Jul 12 '12 at 17:53
add comment

Your Answer

 
discard

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