I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.
While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.
In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.
If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?
As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.
Best Answer
You are correct that updating an non-indexed column will not cause changes to the indexes. In a simple case, there would also be no overall impact on the table.
If a query can use the Index to look up data, it may speed up the lookup, but the exact behavior (depending on your SQL brand) may differ from other brands of SQL. (I use Microsoft SQL Server primarily.)
Of course, updating a column with a significantly greater volume of data could cause some moving of rows to different pages, et cetera.