What does an index actually cost on an INSERT or an UPDATE?
Every index has to be kept up to date, so every write pays for all of them.
So what exactly happens on an insert? The row goes into the table, and then the engine puts an entry into every index on that table. That is one more write for each index, and it is a write that is random because the index is sorted and the new entry goes wherever it belongs.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- Three indexes, because of the key and the two rules.
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_amount ON orders (amount);
CREATE INDEX idx_orders_status ON orders (status);
An update is worse than an insert, because it can touch more than one index. If you change the customer number, then the entry in the index on the customer number has to be moved. If you change the status, then the entry in the index on the status has to be moved. And a delete has to remove an entry from every index as well.
So the cost of the indexes on a table is paid on every single write, and it is paid whether the query that needs the index runs today or not.
The next question in this topic looks at how to decide whether an index is worth keeping.