How does an index affect insert and update speed?
An index speeds up reads, but slows down inserts and updates.
Why
When an INSERT, UPDATE, or DELETE runs,
the database not only has to change the data in the table,
it also has to update every index that field is part of.
Example
If there's an index on email,
and this runs:
sql
UPDATE users SET email = 'new@mail.com' WHERE id = 1;the database changes email in the table and rebuilds the value in the index.
Summary
SELECT: faster with an index,INSERT,UPDATE,DELETE: slightly slower, because indexes require extra write operations.
Short Answer
Interview readyPremium
A concise answer to help you respond confidently on this topic during an interview.