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.