Indexing for an SQL Statement

Question

I need to optimize some sql queries using indexes. I have queries with select, insert and delete. Obviously, indexes are useful when selecting queries. But what happens with insert and delete requests? Does the use of indexes affect their performance?

Answer 1

Yes.

Indexing is a trade-off between query performance and write performance. You must carefully select the indexes that you need in order to get the desired read performance by analyzing the most frequently used queries; while monitoring the costs of insertion / update.

While indexing helps improve query performance, indexes must be supported, and there is a cost when updating or pasting.

This is why (in addition to size limits) we do not just index each column of the table: the update performance ends.

Answer 2

Yes. Indexes should be used sparingly and appropriately to optimize read requests. Insertion and deletion will suffer in performance, the more indexes you add.

Answer 3

Yes, because for each insertion, update, or deletion, the database engine must not only modify the table itself, but also change any index that is affected by the action.

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

Because of this, indexes should be added sparingly and, with the exception of some obvious cases (primary keys / unique constraints, etc.), only when there is a performance problem (or can be reliably predicted).