Question about indexing 1

[pandatime]

Hi,

I am wondering is it worthwhile to index a column in a table with 500 million rows if the column on only has 5 distinct values (and nulls) in it?

The column is a varchar(255), but the longest value is about 10 chars long.

Thanks

 

[Qik3Coder]

Depends on what your other key columns are, and if you are doing joins based on this column.

If you have built a heavy query with this column as one of the key joining fields, then an index is worthwhile.

Every database is different, but the concepts are the same.

Lodlaiden

You've got questions and source code. We want both!

 

[gmmastros]

If you have a table with 500 million rows and you create an index on a column where there are only 5 different values, SQL Server is likely to ignore the index when running your queries.

See here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-the-selectivity-of-an-index

Basically, if most of the data is not selective enough, it would take SQL Server longer to use the index data than it would to do a table scan (or clustered index scan).

This concept changes drastically when there are additional limits on the data. For example, if you are joining tables with a high selectivity and filtering on a low selectivity column, you could create an index on the high selectivity column and include the low selectivity column. A lot of this depends on the queries involved and also what the data looks like.

If you want to post your query and indicate the column that only has 5 distinct values, we may be able to offer suggestions regarding indexes to help speed up the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gwilson67]

That wouldn't be a good choice for an index because it has a high number of duplicates.


Greg

http://www.freewebstore.org/tsqlcoderepository

Powerful tool for SQL Server development