Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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?

share|improve this question

1 Answer

up vote 3 down vote

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.

share|improve this answer
I do have SELECT against that column - and a lot of them. – sharptooth Jan 17 at 13:57
@sharptooth I apologize, I read the "rarely" part and thought that was regarding a SELECT query (I usually see updates called statements, not queries). Still, with a very small range of possible values, it is unlikely that an index will help. But you can always test it! – Aaron Bertrand Jan 17 at 14:02
How do I make sure there's no serious negative impact from my change? – sharptooth Jan 17 at 14:03
1  
@sharptooth test it somewhere other than production? – Aaron Bertrand Jan 17 at 14:11
That's not really possible because it'd be very hard to simulate exactly the same load patterns. – sharptooth Jan 17 at 14:15
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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