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.

I have a table called Products with ID(PK, Clustered), Name, Price, Brand, ...., RetailerID, SKU, ModelNumber. My query have

SELECT .... 
FROM Products 
WHERE RetailerID = @RetailerID AND 
      ( (@SKU != '' AND SKU = @SKU)  OR (@ModelNumber != '' AND ModelNumber = @ModelNumber))

I am going to add index for this query. Should I add 3 indices on RetailerID , SKU and ModelNumber or 2 compound indices on (RetailerID, SKU) and (RetailerID, ModelNumber) or 1 compund index on (RetailerID, ModelNumber, SKU)

share|improve this question
1  
I would try the 2 compound indexes (because in some cases only one of the 2 would be useful) and rewrite the query with UNION. –  ypercube Jan 2 at 10:28
    
@ypercube . A UNION is almost always a bad idea in terms of performance –  MichaelD Jan 2 at 10:36
    
@ypercube, Thanks, what do you mean with UNION? –  user960567 Jan 2 at 10:45
    
@MichaelD I agree. This is one of the situations which I'd expect to fall in the "almost" case. And I don't mean that UNION is 100% the best approach here. But any way that can force the optimizer to use the 2 indexes might yield a more efficient plan. –  ypercube Jan 2 at 11:23
add comment

1 Answer

up vote 1 down vote accepted

I'd create a seperate index on SKU and ModelNumber. I don't think an index on RetailerID will do much good as there won't be many different retailers .

The best approach to analyze this is by comparing the execution plans & times of the different sets of indexes

share|improve this answer
    
Thanks Micael. Can you explain a little more how to rewite this into UNION and why it is bed? –  user960567 Jan 2 at 10:53
    
You don't want to rewrite this in a union :) . Here's some more info about the union syntax: technet.microsoft.com/en-us/library/ms180026.aspx –  MichaelD Jan 2 at 10:58
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.