0

(On MySQL, InnoDB) I am not directly checking if a certain record exists, instead I do that check lazily : I am normaly using INSERT ... ON DUPLICATE KEY UPDATE .... At some points of my code, the probability of an existing record is higher than absence of it, in that case I plan to do:

  • UPDATE ...
  • If affected_rows<1 INSERT IGNORE INTO..

Would this perform better than using INSERT ... ON DUPLICATE KEY UPDATE ...?

frankish
  • 6,738
  • 9
  • 49
  • 100
  • Generally speaking, you should pick the best method for each specific situation. – Dan Bracuk Dec 09 '15 at 18:04
  • 1
    It depends on a lot of factor. If your server and your client are not on the same hardware, `INSERT ... ON DUPLICATE KEY UPDATE` might be better because it does not require a second round-trip. – Richard St-Cyr Dec 09 '15 at 18:05
  • I've never been a fan of the `ON DUPLICATE KEY` method; every developer I've seen use it regularly seems to end up getting bitten by it eventually. However, I have heard some strong arguments in favor of it to prevent concurrency issues on busy systems. All that said, affected_rows may not a good solution either, depending on the `SET` of the update; affected_rows will be 0 if the row(s) exist and the sets do not change the current values. – Uueerdo Dec 09 '15 at 18:15

0 Answers0