Is it a good idea to have an index when the column value changes several times over record lifetime? I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're maybe three to seven such changes. Meanwhile I have thousands of queries that SELECT or UPDATE rows with specific State values. One rarely run UPDATE changes all rows that have 4 to 5.

I feel like adding an index on State column would be a good idea. Yet since State can change it means the index will have to be changed on every such change.

Do I add an index or not? How do I make a decision?
If the query against this column is rare, then will you really gain from it? Depending on cardinality, the index might not really help anyway, or even be chosen to satisfy the query. And remember that it has to be maintained 100% of the time.