MySQL - Simple update is very slow

There are many possible explanations:

  • UUIDs have terrible performance on large tables.

  • What was the value of innodb_buffer_pool_size? It should be about 70% of available RAM. Poor caching, especially due to UUIDs could ba a problem.

  • That UPDATE needs INDEX(SourceId, ParentKey) (in either order).

  • Why prefix the index on OperationId when it is already that length?

  • Don't use utf8 on hex strings.

  • Packing UUIDs into BINARY(16) would shrink the table, thereby providing more speed.

  • Updating 80K rows at once takes a lot of effort, especially when planning for a possible ROLLBACK. This may partially explain why InnoDB seems to be slower than MyISAM. Will you often update that many rows? Sound like a design flaw.