Re: Insert Into ... Select, Delte Data

[Rick James]

Ping-pong between two tmp tables. That is, at any one moment, your data-generators are inserting into one tmp table (say. `t1`), while your other script is doing the INSERT ... SELECT FROM `t2` and TRUNCATE TABLE `t2`.

The flip is done by that second script after the TRUNCATE, via:
RENAME TABLE t1 TO x, t2 TO t1, x TO t2;
This is atomic and very fast; it will hang the other script(s) only briefly. Being atomic, there is no risk of missing some inserts.

I don't think there needs to be any timer -- the INSERT-TRUNCATE-RENAME script can simply run as fast as it can.

However, this technique is not really needed unless you have millions of rows in the 'real' table, and data is arriving at a fast rate (hundreds per second).
As Peter says, why have the extra table?