Update first, if not then insert (mysql)

Posted on

Question :

I want to do something like insert on duplicate key update but reverse i.e. first it should try to update, and if it doesn’t exist, then it should insert.

Why? Because I am running a large number of these queries and most of them are actually update queries and not insert queries. So if I use insert on duplicate key update it takes longer because it’s actually 2 queries. So if I can somehow do the reverse it will be ideal.

Any suggestions? If it’s not possible, then how do I make this more efficient. Thoughts?

Answer :

This is possible with MySQL command line “Load data file”. But it no SQL query.

13.2.6 LOAD DATA INFILE Syntax
https://dev.mysql.com/doc/refman/5.7/en/load-data.html

I don’t think it is an issue.

Look at what IODKU has to do — whether it tries INSERT first or second.

  1. Locate the row(s) based on the unique key(s) — If things are not completely cached, then this is the slowest step.
  2. No row — INSERT. Note that the block(s) needed are sitting in the buffer_pool.
  3. Row exists — UPDATE.

Furthermore, the INSERT must be tested because it can succeed or fail. The UPDATE does not have success/fail.

Leave a Reply

Your email address will not be published.