MDEV-515 InnoDB can handle bulk inserts better. But still that optimization only applies to the first INSERT into the empty table. It works well for INSERT...SELECT and LOAD DATA, but, for example, mysqldump generated dumps contain a series of INSERT statements, and that usage is still poorly optimized.
The problem here is that in the bulk insert mode InnoDB cannot rollback individual statements, all the work done in the bulk insert mode is atomic, as a whole.
There should either be some way to express it in SQL or make sure there will be no rollbacks.
Another complication is that in the bulk insert mode InnoDB locks the whole table, not individual rows.
A typical sql dump looks like
It seems that with UNIQUE_CHECKS=0 and FOREIGN_KEY_CHECKS=0 there should be no rollbacks. But with CHECK constraints there still should be? And as inserts are done under LOCK TABLES, InnoDB fine grained row level locking is wasted here. So, it seems to be possible to get
MDEV-515 to work with no syntax extensions in the specific case of mysqldump output.
To make it work generally we'll need a new syntax that will mean "this block of work can only be rolled back as a whole"
A standard approach would be something like
which means pretty much exactly what we want to do. But the syntax is, perhaps, too awkward?
Non-standard suggestions, copied from