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.
- Locate the row(s) based on the unique key(s) — If things are not completely cached, then this is the slowest step.
- No row —
INSERT
. Note that the block(s) needed are sitting in the buffer_pool. - Row exists —
UPDATE
.
Furthermore, the INSERT
must be tested because it can succeed or fail. The UPDATE
does not have success/fail.