Take the 2-minute tour ×
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.

First of all Good morning !

Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!

Now come to the point,

Suppose I create 20 indexes ( obviously non-clustered) on one table (which have id,name,username,password). It will work fine.

Now the point is if I modify my table as (id,name,username,password,status,varification_code) and indexes are remains 20. Will it effect the performance ?

And what will be if indexes increase or decreases and table remains same ?

And what will be if indexes increase or decreases and table modified ?

share|improve this question

migrated from stackoverflow.com Feb 24 '12 at 13:40

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

Every time you update a record in the table, all indexes whose entries have changed because of that also need to be updated. So there more indexes you have, the longer the update will take.

Usually that is not a concern, but if you bulk-load a lot of table, it might be a good idea to disable all indexes (and constraints) and re-enable them after the load is done (as index rebuilding is faster than continuous updates).

share|improve this answer
add comment

The number of indexes will affect the inserts and and updates you do on your table. If you add more columns there is not going to be an inversely decreasing performance due to the current 20 indexes. Just the extra effort needed to populate the new columns.

If you remove the unwanted indexes, it will improve the read/write performance on your table. Do you need more indexes that the number of columns in your table?

share|improve this answer
1  
"If you remove the unwanted indexes, it will improve the read/write performance on your table": How does an unnecessary index affect read performance? –  Thilo Feb 25 '12 at 1:48
    
Examples would be a key lookup or a RID lookup. –  StanleyJohns Mar 2 '12 at 2:35
    
How does an unnecessary index negatively effect read performance for a key lookup or a RID lookup? That index is not going to be touched at all, right? –  Thilo Mar 2 '12 at 23:57
add comment

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.