I have a question about index performance. This is my scenario: Table A consists of 10 columns. Col1 is the primary key. I have three indexes
for Table A: IDX1 consists of Col2, IDX2 consists of Col1, Col3, Col4 and Col5 and IDX3 consists of Col4, Col5, Col6 and Col7. These
indexes are helpful when I perform select statements, especially for SQL involving GROUP BY. Will these indexes affect the performance on updates and inserts for Table A? Without these indexes, the select statements are causing a lot of problems in performance and are thus resulting in high-cost execution and elapsed time is high.
Table A --------- Col1 PK Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10Thanks and regards.
Requires Free Membership to View
All indexes cause overhead (for index maintenance) on DML (insert, update, delete). You must assess whether the performance gain on selects, updates and deletes (all of which can include a predicate, or WHERE clause) is worth the overhead. In your case, the table does not appear to be over-indexed, and you gain significant performance advantages by having the indexes, so you should keep them unless DML is unacceptably slow.
This was first published in July 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation