MDEV-515, the original intention was to do 2 things to speed up INSERT to an empty table or partition:
- Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
- When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.
The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up ROLLBACK and recovery, and also the purge of history, compensating for
MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.
The purpose of this task is to make INSERT into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (
MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)
After implementation comments:
The following queries will use the new optimization:
- CREATE ... SELECT
- Inserting into an empty table together with SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR ...
- INSERT statement
- INSERT ... SELECT
- LOAD DATA INFILE
Insert into an empty table without unique_checks=0,foreign_key_checks=0 will NOT be optimized.