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
needsINDEX(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.