asked
Help with the proper index
I have a table with a lot of columns. In a particular query being generated by a web API it selects 23 columns of data. With 4 of those columns in the where clause. I am assuming that in order to avoid any bookmark lookups that I would need to create an index on the most selective column and then include the remaining 22 columns as included columns. Is this the correct approach? I ask because in my testing the only way I have avoided RID lookups is by including all the columns as included columns and the primary index being on a very selective column. Any pointers, gotcha's and assistance is very much appreciated.
23 columns is a lot to store on the page of an index, depending on the data we're talking about. However, if you are doing point lookups on this, then it might be worth it. You'll need to test to be sure. Instead of a single column as the key, I'd probably plan on using all four columns from the WHERE clause in the key. As a starting point for testing, yes, I'd pick the most selective column, but I would test with other columns too.
We are totally in "it depends" territory. Yes, in general I would go with the two most selective columns as the leading edges on the index, but, that's where I would start testing, not necessarily end it. If those two columns are used in lots of queries, having the other two won't change negatively places where an index on just those two columns would also work. But again, it depends on the types of queries we're talking about, point lookups versus scans versus aggregations. Just remember, if those two columns are required to turn the scan into a seek operation, they might be worth storing in the key. Don't be dismissive just because they're not selective across the entire data set.
What if two of the columns in the where clause are very selective, but the other two are not at all. For example one may only have an option of 40 values but have 300M records. Also the two selective columns in the query would also be used in nearly every query on the system. Should I use the two most selective in the key and the remaining as included so this index could be used much more frequently by other queries. I am thinking yes.
12 People are following this question.
