performance of Replace vs If exists update else insert vs update if row_count()=0 insert

Question

Structure of my table is:

Id int(11) NO PRI auto_increment
Mobile varchar(10) NO 
LoanAmount int(11) NO 

I want to UPDATE if existing Mobile else INSERT new entry.

What have I tried?

• Using REPLACE as there is no foreign key constrain
• IF EXISTS (SELECT 1 FROM table WHERE Mobile='SomeValue') UPDATE ... WHERE Mobile=v_mobile ELSE INSERT INTO table ...
• UPDATE table SET (...) WHERE Mobile='SomeValue' IF ROW_COUNT()=0 INSERT INTO table.person (...) VALUES (...)

Which would be best in terms of performance or future maintenance?

Answer 1

Usually the answer is simply "which one sends more requests to the server".

• IODKU - 1
• REPLACE - 1
• EXISTS - 2 (SELECT, plus either UPDATE or INSERT)
• ROW_COUNT - 1 or 2 (ROW_COUNT does not count)

But we have a tie with that metric. So, let's dissect the winners:

• IODKU - Find the row, decide what do, then do it.
• REPLACE - DELETE all rows that match on any UNIQUE (or PRIMARY) KEY, then INSERT.

It feels like IODKU will be less work.

• bhagat: As you can see in the table, Id is the primary key. I want to replace duplicate of Mobile. Would that be possible using IODKU?
• Rick James: You need UNIQUE(mobile) for REPLACE to work. And for IODKU to work. Furthermore, your SELECT and UPDATE will be slow table scans without some kind of index. That is, your code will be twice as slow.