2

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?

5

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

InnoDB Architecture

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.

  • Is there a link to the original image? – Pacerier Feb 23 '15 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.