Does a database update force index rebuild regardless of updated field?

Question

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated.

For example, if I have a table with 20 fields, the first 10 of which are referenced in either individual or covering indexes, is the speed of updates to the other 10 non-indexed fields affected?

Answer 1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko)

When you update an indexed column, changes must migrate through this architecture as follows:

• From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1
• From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file)

If you are updating a non-indexed column, the only bottleneck would just be the flushing changes in dirty page over to the .ibd file over the Write I/O threads. Index change buffering would not be involved for non-indexed columns.

If you have MySQL 5.5/5.6, you may want to increase innodb_write_io_threads and innodb_log_buffer_size for increased write performance.