Replace into speedup

Replace into speedup

When executing replace into SQL statements on large tables with random primary key insertions, performance is limited by the random read rate of the storage system. For some types of tables and some MySQL configurations, TokuDB can avoid doing reads when executing these statements, which can result in a huge gain in performance.

Effect

If a row with the given primary key exists, then replace it, else insert a new row.

Implementation

Query the tree for a row with the given primary key. If the row does exist, then update the row, else send an insert message into the fractal tree.

Performance

A read I/O may be required if the insertion primary key pattern is random and the table is larger than memory. Application throughput is limited to the random read rate of the storage system.

Objective

Speed up replace into by skipping the query and just sending an insert_maybe_overwrite message into the fractal tree. This will replace the row if it exists. Otherwise, it will insert a new row.

Limitations

1. Table must only have a primary key.
2. No triggers defined.
3. Binlog is on and statement logging is being used.