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.
Thanks and regards.
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.