Composite index

Assuming I have a table X with 12 columns. In my queries I am filtering on these conditions:

select * from x where a=@a and b=@b

select *  from x where a=@b and b=@b and c=@c

select * from x  where a=@b and b=@b and d=@d

select * from x where a=@b and b=@b and e=@e

This table is very active table and I need to avoid blocking. If I need to create indexes on this table should I create 4 indexes like this:

(a,b) (include columns) 

 (a,b,c) (include columns) 

 (a,b,d) (include columns) 

 (a,b,e) (include columns)

Or like this:

(a,b) (include columns)

(c)

(d)

(e)

Or
create 1 index (a,b,c,d,e) (include columns)

These are the counts of distinct values for each column. total rows 1446631 , a = 366279 , b= 96 , c = 6 , e = 2 , d= 11098

If all of your query patterns include filters on both a and b, and the table gets written to a lot, I would argue for testing a single index that looks like this:

(a,b) include (c,d,e,other include columns)

The reason is it's a single index to maintain rather than three or four, and the selectivity of the third column in the filter probably won't add much additional benefit to individual queries since the first two columns already filter out most of the table (well, really, the first column, since the second column has very few distinct values). You can monitor the queries and if see if any patterns or particular parameter values lead to horribly bad estimates, long runtimes, or missing index warnings. I suspect they won't because, again, based on the density you've quoted, this should lead to very small ranges as long as your queries use filters on a and b or, at the very least, a.

I will stress that where you end up is not going to be a simple "oh, this is clearly what you should do" answer. You will need to test with your hardware, your data, and your query patterns (both read and write) to determine the best index(es) for your workload.

StackExchange Database Administrators Q#139749, answer score: 9