In this post two weeks ago, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. Towards the end of the post, I claimed that it would be better to use “replace into” or “insert ignore” over normal inserts, because the semantics of these statements do NOT require disk seeks. In this post, I explain how the command “replace into” can be fast with fractal trees.
The semantics of “replace into” are as follows:
- if the primary (or unique) key does not exist, insert the new
- if the primary (or unique) key does exist, overwrite the
existing row with the new row
The slow, expensive way B-trees use to implement these semantics