After populating the stock_tmp table, i'm running these SQL queries via legacy mysqli wrapper code. I don't think this code needs additional locks to prevent people from seeing incorrect data. Is that correct?

DELETE FROM stock WHERE ean NOT IN (SELECT ean FROM stock_tmp);
REPLACE INTO stock SELECT * FROM stock_tmp;

1 Answer 1

INSERT INTO stock SELECT * FROM stock_tmp AS tmp
ON DUPLICATE KEY UPDATE ean=tmp.ean, etc=tmp.etc;

Is 10+% faster than REPLACE INTO and does not change all AUTO_INCREMENT data. Source.

10+ times faster is this:

RENAME TABLE stock TO stock_swp,
stock_tmp TO stock,
stock_swp TO stock_tmp;

The implied locking should be sufficient in any case.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.