Sfrgttk

I'm using a statement similar to the following to append data to a column of type mediumtext to a bunch of rows:

INSERT INTO myTable (myKey,myVal)

ON DUPLICATE KEY UPDATE myVal=CONCAT(myVal,VALUES(myVal))

VALUES (1,'foo'),(69,'bar'),(1337,'baz')

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

This mysql bugreport brings up exactly this issue:
https://bugs.mysql.com/bug.php?id=47937

Is there any way of making this faster?

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

MySQL is an MVCC database. It's one the results of an MVCC that rows must be rewritten entirely on update. It may be even more surprising to you, but it's considered an optimization if the index does not need to be written to when you're rewriting a row.

Assuming that mysql already knows the length of the field as part of the record storage, it should be possible to append data to a field without having to read it, so while the original query reads and writes the whole existing string, it should be possible to optimise that by recognising that source and destination fields are the same and simply appending to the existing value and increasing the stored length. This would return the complexity to a more manageable O(n), and thus give reasonable performance.

That would leave others vulnerable to a "dirty read." Likely there are too many real issues facing developers of MySQL to worry about this non-issue. You can't just write to a tuple. What if it's in someone else's snapshot? What if you want to ROLLBACK later? This is just simply not the way MVCC works. You copy and modify. Then you commit and flush.

My guess is the answer is NO. You won't make this any faster.

A database (at least all the ones I've worked with) doesn't treat text as you would with a Java StringBuilder, but rather like a Java String, which will suffer exactly the same problem (as would strings in most programming languages, except, maybe the V8 implementation of JavaScript). I wouldn't consider that a bug. It's a design decision. A database is not normally used in this fashion.

A StringBuilder has extra room for extra text, which is appended at the end of the already used space. When it actually runs out of space, it allocates a big chunk for further filling. A String is immutable and does not have extra room to add new text "at the end".

Perhaps what you should do is not modify your original row, and have instead a secondary related table where you store every string associated with myKey, together win an string_order column (could be an auto_increment, or a current timestamp) and, when needed, retrieve everything together with a GROUP_CONCAT of them. Your related table would just work as a kind of log, where you would be inserting the new "events" in order, a piece at a time.