Indexing for SQL statement

Question

I have to optimize some sql queries by using indexes. I have queries with select, insert and delete. It is obvious that indexes are useful in case of select queries. But what happens with insert and delete queries?Does the use of indexes affect negatively their performance?

Answer 1

Yes, because for each Insert, Update, or Delete, the database engine has to not only perform the modification to the table itself, it also has to modify any index that the operation affects.

The degree of this effect is of course dependent on the index (how wide it is, whether it is clustered or non-clustered, how many disk IOs are required to perform the index update, etc.

Because of this, indices should be added sparingly, and, except for some obvious cases (Primary Keys/unique constraints, etc.), only when a performance problem exists (or can be reliably predicted).

Answer 2

Yes.

Indexing is a compromise between query performance vs. write performance. You should carefully choose the indexes that you need in order to obtain the desired read performance, by analyzing the mostly used queries; while keeping an eye on the insert/update cost.

While indexing helps performance while querying, the indexes has to be maintained, and there is a cost for that when updating or inserting.

This is also why (besides from size constraints), we do not just index every column of a table: Update performance would come to a grinding halt.

Answer 3

Yes. Indexes should be used sparingly and appropriately to optimize read queries. Insert and delete will suffer performance-wise the more indexes you add.