I'm trying to figure out what is "performance wise" the best choice if you have hundred thousands of rows in a MySQL Database that partially get updated frequently.

INSERT INTO ... ON DUPLICATE KEY UPDATE

This is my current approach where I just insert/update the whole row without checking what actually changed.

SELECT FROM ... WHERE ID=? and let PHP Check if something was found

Would this be a better/faster solution? So I trigger an INSERT statement and if the ID exists, I UPDATE the whole row.

Or maybe should I check a step further (in case ID exists) and compare the found row against the updated row in PHP, and then ONLY update the values that have actually changed?

LOAD DATA Statement

I have never used this so far and would need to get familiar with, but maybe using this with a REPLACE parameter would be another approach that might speed up the whole import.

CodePudding user response：

Technically an insert will always change an indexed row causing the index to be remade by the engine. But if your update changes the index it's a mute point as the index will still be remade.

This explained in length here: https://stackoverflow.com/a/15412865/11005071

CodePudding user response：

I would say your first approach is much better in terms of efficiency and performance. Using simple, shorter lines of code is more efficient than writing query over several lines. Provided you achieve your intended purpose. And whether you use the second method or first approach should not be your key motivator. Your real objective should be readability, maintainability, and efficiency.