At 250/hour, all possible techniques are plenty fast. Anyway, I will throw my 2 cents in...
INSERT of 100 rows will run 10 times as fast as 100 individual
INSERT may lead to gaps in
AUTO_INCREMENTs. In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use. (
REPLACE burns lots, since it is
For efficient "normalization" do something like:
INSERT IGNORE INTO HostNorm (host_name)
SELECT DISTINCT s.host_name
FROM Staging AS s
LEFT JOIN HostNorm AS n ON n.host_name = s.host_name
WHERE n.host_id IS NULL;
- A list of possibly new values is in
HostNorm is where 'new' rows need to be put.
- Only 'new' rows are fed to
IGNORE is to accommodate having multiple threads (connections) doing similar inserts that could collide. (A rare collision would lead to a burned
- There is no cap on how many rows this can handle.
- This works best with, say, 100 rows.
- This is designed for very high speed input; it should work fine for your relatively slow ingestion rate.
More discussion in my blog.