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.

Answer 2

Plus to what was given above. I'll give you my index suggestion for your specific query:

--Added to show you how you use the SELECT columns in the IX INCLUDE section.

SELECT t1.col1, t1.col2  
  , t2.colA, t2.colB
FROM t1
  LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid)
WHERE t1.enabled = 1 AND
      t1.startDate <= '2021-02-26 19:03:04.437' AND 
      (t1.counter = -1 OR
       t2.counter IS NULL OR
       t1.counter > t2.counter)

--Index1:

create index ix_t1 on t1 (enabled, startDate, counter) include (col1, col2, id)

--notice i added column "id" because if it's your PK it helps to add it to the INCLUDES

--Index2:

create index ix_t2 on t2 (counter) include (colA, colB, userid)

-- assuming useid is your PK.

that's my attempt at indexing it. I maybe off just a little bit so please test it and adjust as needed.

Also I would highly recommend rewriting that query and substitute the OR with UNION. These queries with OR's have a tendency to go row-by-row and are very slow on large tables. The UNION version should look something like this. (again please test it I may be off just a bit):

--1st OR

  SELECT t1.col1, t1.col2
  , t2.colA, t2.colB
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 
UNION--2ND OR
  SELECT t1.col1, t1.col2
      , t2.colA, t2.colB
    FROM t1
      LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid)
    WHERE t1.enabled = 1 AND
      t1.startDate <= ??? AND 
      t2.counter IS NULL OR
UNION  --3rd OR
  SELECT t1.col1, t1.col2
      , t2.colA, t2.colB
    FROM t1
      LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid)
    WHERE t1.enabled = 1 AND
      t1.startDate <= ??? AND 
       t1.counter > t2.counter

good luck.