Do indexes make db updates slower?

Question

It is usually said when you create indexes, it makes fetches faster for you but on the same time they slow down your updates (delete,insert,update) as with every update indexes are to be recreated.

I have a question in my mind: if database updates its indexes after update operation and returning from query then how does this slow down the query execution?

As query is returned in its normal time after that indexes are being updated. We may say that indexes may cause increase in databases internal maintenance work but they should not increase update query time.

Answer 1

Database indexes make database updates slower and faster at the same time. This depends on the update statement:

• When you have an update on all rows like update mytable set mycolumn = 4711 then index creation will slow down the update, because it is some extra work that needs time.

• When you have an update statement with a where condition like update mytable set mycolumn = 4711 where mycolumn = 123 then the update is speed up by the existing index, because the database has not to do a full table scan, instead it can use the existing index on mycolumn.

• So from the above you can say that inserts will always be a bit slower

• Also you can say from the above that delete's with where clause will also be faster.

• Last but not least selects will be much faster!

The point of indexes is that they are a kind of compromise - you should see them from an average view for all statements you send to the database - when the indexes are chosen very well they will speed up the database access a lot. Without indexes most things (select, update, delete) will be much slower. So you really should not worry about the small slow down that an index might bring to an insert.

For the case that you have a performance problem on update's you should first check if the update's where clause will do a full table scan that slows down the update!