CODEWITHSUNDEEP
mysql
Lee Loftiss
Lee Loftiss

Reputation: 3205

Which technique is more efficient for replacing records

I have an app that has to import TONS of data from a remote source. From 500 to 1500 entries per call.

Sometimes some of the data coming in will need to replace data already stored in the dB. If I had to guess, I would say once in 300 or 400 entries would one need to be replaced.

Each incoming entry has a unique ID. So I am trying to figure out if it is more efficient to always issue a delete command based on this ID or to check if there is already an entry THEN delete.

I found this SO post where it talks about the heavy work a dB has to do to delete something. But it is discussing a different issue so I'm not sure if it applies here.

Upvotes: 2

Views: 58

Answers (2)

Jaylen
Jaylen

Reputation: 40381

Since you are using MySQL and you have a unique key then let MySQL do the work.

You can use INSERT INTO..... ON DUPLICATE KEY UPDATE......

MySQL will try to insert a new record in the table, is the unique value exists in the table then MySQL will update all the field that you have set after the update

You can read more about the INSERT INTO..... ON DUPLICATE KEY UPDATE...... syntax on http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Upvotes: 0

user207421
user207421

Reputation: 311018

Each incoming entry has a unique ID. So I am trying to figure out if it is more efficient to always issue a delete command based on this ID or to check if there is already an entry THEN delete.

Neither. Use INSERT ... ON DUPLICATE KEY UPDATE ....

Upvotes: 3

Related Questions