Index on Column with only 3 status?

Question

I have a table with millions of rows. This table has a column which we frequently filter(means we use it in WHERE clause frequently), the values are Insert/Update/Ignored. Is it make sense to add index to columns? Someone told me adding index on few values column have no benefit

Answer 1

If you have index with just the status field, most likely it will not be used because for each row found from the index the rest of the columns you select must be fetched from clustered index and these key lookups will be expensive and most likely it is more efficient just to scan the clustered index.

If you have other fields in the where clause, a composite index should work better. Having the status field there depends also on the fact if it is being updated (often) since that will then require maintaining the index too.

One option is to add the fields you have in select part as included columns. That way the query doesn't have to go to the clustered index and should work quite fast. You should try this to see if it actually works in your case. There will of course be maintenance overhead that depends on several things, especially how often the fields included in the index are updated.

If there is one status that is rare and is used to fetch rows, then filtered index might work, but I would assume that's not your case.

Answer 2

Adding index on low cardinality column can make sense for instance in case you need to search for value which is quite rare - say you have column State with possibities of New/Processed and 95% rows set as Processed, and you have to get those New often - then index will help you find them fast. But having it present means optimizer can try to use in bad cases.

Usually it is suggested to not index such column by itself, but use it as part of multicolumn index which can help specific queries (for instance - you want to process those new records from oldest to newest, then you add index on (State, CreatedTime) and query it like "where State = 'New' order by CreatedTime ASC" and this index will let you have those New fast and already ordered.