SQL Server Performance Forum – Threads Archive

composite index selectivity

I read in the quote below that only the first column in a composite index is judged for selectivity by the query optimiser, is this true? Does this mean that if for example: I had a composite index on C1,C2,C3 where C1 is 80% unique and C2 85% and C3 90%. If I query this table specifying C1, C2 and C3 even though my query is very selective(returns 1% of rows) the optimiser is unlikely to use the index as C1 is less than 90% unique. "… Sometimes, it is a good idea to split a composite index into multiple single-column indexes. This is because only the first column in a composite index has statistics stored for it. And if this first column is not very selective, it may not be used by the Query Optimizer…" –http://www.sql-server-performance.com/composite_indexes.asp Thanks in advance …

SQL Server has a histogram only in the first column of the multicolumn index and has density information for all initial prefixes. In addition to scanning the table in the shown index, the optimizer investigates other indexes that exist in the table. If the C1 is unique (or almost unique), another good plan is to have a single-column index only for the column_id. In such a case, the index seek would be followed by a fetch of the corresponding C2, C3 column values from the base table, with or without a clustered index.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If you really need SQL server to use the secondary columns of your index in data retrieval, you MAY want to create and update the column statistics yourself. I wrote a script to do this some time back. Have a look at it if you are interested at: http://www.geocities.com/vbkenya/secondarycolumnstatistics.htm] Nathan H.O.
Moderator
SQL-Server-Performance.com

Couple of guidelines about composite indexes:
– Always put the columns in order from most selective to least selective for the above mentioned reason.
– Avoid creating composite indexes as clustered. The reasoning here is, that the clustered index keys are stored in the leaf level of the non clustered indexes on that table. The wider the composite clustered index the wider the nonclustered indexes, thus fewer rows fit on a page, more IO is needed to retrieve the information.
– If you frequently need to search for the nonfirst column, consider creating a seperate index for the above mentioned reasons. HTH
—
–Frank
http://www.insidesql.de

]]>