We are having trouble with simple updates on a single table taking a long time. The table contains ~5 Million rows.

Here is the table:

CREATE TABLE Documents (
    GeneratedKey varchar(32) NOT NULL,
    SourceId int(11) NOT NULL,
    Uri longtext,
    ModifiedDateUtc datetime NOT NULL,
    OperationId varchar(36) DEFAULT NULL,
    RowModifiedDateUtc datetime NOT NULL,
    ParentKey varchar(32) NOT NULL,
    PRIMARY KEY (SourceId, GeneratedKey),
    KEY IX_RowModifiedDateUtc (RowModifiedDateUtc),
    KEY IX_ParentKey (ParentKey),
    KEY IX_OperationId (OperationId(36))

Here is the update query:

UPDATE Documents
SET OperationId = 'xxxx'
WHERE SourceId = 12345
AND ParentKey = '0965b3983ceb0e8e41ab47b53e37d0f3';

This query updates ~80k rows and takes around 60 seconds to complete, and the more rows updated the longer it takes which results in timeouts. The index IX_ParentKey cardinality is ~830k. Note that a select with the same WHERE clause returns very quickly (< 1s).

Query profiling:

starting                0.000072
checking permissions    0.000006
Opening tables          0.000019
init                    0.000023
System lock             0.000266
updating                50.415424
end                     0.000039
query end               0.024398
closing tables          0.000037
freeing items           0.000051
cleaning up             0.000022

When switching to MyISAM, the query took only 2 seconds. What could be slowing down the updates, I assume the index needs to be rebuilt? Is there any way we can optimize this?


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.

  • innodb_buffer_pool_size was already pretty high. I modified the index but the performance is the same. You are right about the design though, we are doing lots of single-row updates and sometimes big updates like this, only when needed. – MicG Sep 14 '17 at 17:29
  • for the utf8 suggestion, does this need to be changed on the whole table? Also, what would be your suggestion to replace the UUID ? They are actually a MD5 of the Uri column, since this column was long text and an index on this did not work well. – MicG Sep 14 '17 at 17:49
  • MD5 is just as bad as UUID for randomness. More discussion here: mysql.rjweb.org/doc.php/uuid . As for utf8: at least on all columns containing only hex characters. – Rick James Sep 14 '17 at 21:53
  • For testing uniqueness of a URI, yes, a MD5 is reasonable. But, again, it should be either CHAR(32) CHARACTER SET ascii or BINARY(16) and use UNHEX(...) when INSERTing and testing. – Rick James Sep 14 '17 at 21:55

InnoDB in MySQL is really not well adapted to this type of data volume. Also I suggest you, if it's possible and if your DB doesn't have foreign keys to swith to the MyISAM engine.

Meanwhile, you should be able to improve performance by creating BTREE and/or UNIQUE indexes on your sourceId and parentKey columns.

  • 5 millions rows are nothing on any technology, provided the users understand performance. – Federico Razzoli Oct 5 '20 at 13:55

You should build an index on (SourceId, ParentKey) in this order.

On the contrary, the index on OperationId may be a small part of the problem, but with the above index you should be fast enough.

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.