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

  • What's the schema for this table? Are the three values roughly equal in frequency? Have you tried adding an index and checking your execution plans? May 10, 2015 at 14:35

2 Answers 2


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.


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.

  • 1
    Index on column with data skew may cause quite a few problems for optimizer... I think filtered index ( in case of SQLServer) will be a better alternative.
    – a1ex07
    May 10, 2015 at 17:25
  • Yes, you are right, I forgot the possibility (using mysql more than mssql..). Would you mind if I add it?
    – jkavalik
    May 10, 2015 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.