I am currently learning about SQL queries and how indexes work with them, and I ran into a situation that I don't understand. Let's say I have this query for that updates a tables' values with another table and they both have a dozen columns with a hundred thousand rows each:
UPDATE TA SET TA.Column = TB.Column, TA.ColumnTwo = TB.ColumnTwo, TA.ColumnThree = 3 ... FROM TableA TA INNER JOIN TableB TB ON TA.ColumnFour = TB.ColumnFour
Table TA has 9 indexes on it while Table TB only has 2. This update query would take longer than 5 minutes to update only a few hundred rows that matches the condition
TA.ColumnFour = TB.ColumnFour. I assumed that it was because of the extra indexes since the query execution plan shows 9 Index Update (1 for each index). I removed the duplicate/unecessary indexes and I was able to lower the number of indexes to 4 but the query would still run slow.
My question is why does the update statement also updates indexes and how would I be able to prevent the query from creating the multiple Index Update?