Create multiple indexes vs 1 index (multiple columns) with range column not at the end

Viewed 32

I have multiple columns which I need to index. However, UserId is an optional search and DateOrdered is not optional.

Here (a simplified version) of the select statement

SELECT *
FROM [dbo].[tblOrder]
WHERE ([ProductId] = @ProductId) AND ([ItemSizeId] = @ItemSizeId) AND
      ([DateOrdered] >= @StartDate) AND ([DateOrdered] <= @EndDate) --(optionally AND [UserId] = @UserId)

I am thinking of creating composite index of ProductId_ItemSizeId_DateOrdered_UserId. Alternatively, I can create 2 composite indexes of ProductId_ItemSizeId_DateOrdered and ProductId_ItemSizeId_UserId_DateOrdered

The order records are up to 20 million orders per month. Which approach is better?

Thanks.

0 Answers
Related