Advantage of "LOAD DATA ... REPLACE INTO TABLE" over "UPDATE table_name SET"

Question

I inherited a system in which all updates (even to a single row/record) to MySQL tables are not done using UPDATE table_name SET. Rather, they are done by:

1. Exporting the existing table to a CSV (text) file.
2. Modifying the corresponding row(s) in the CSV file.
3. Reloading the CSV file using LOAD DATA ... REPLACE INTO TABLE.

This is the first time I see such an approach to updating table records and I am wondering what could be the rational for that.

BTW, this scheme results in numerous thread synchronization issues because of the need to lock CSV files while updating.

I would appreciate an explanation or insights on the benefits of using LOAD DATA ... REPLACE INTO TABLE instead of UPDATE table_name SET.

Answer 1

I would guess the previous developer read that LOAD DATA is faster for bulk-loading data. This is stated in the MySQL manual and repeated often on sites like this one.

Then they made a naive assumption that LOAD DATA is faster for everything, even single-row updates.

I'm pretty certain that the developer never measured the performance themselves.

It's very unusual to use LOAD DATA for anything besides bulk-loading data that is already in a file. I would never use it for single-row updates.

If I were in your shoes, I would change that code to use conventional UPDATEs. Don't suffer with the thread synchronization issues.

Answer 2

I would appreciate an explanation or insights on the benefits of using LOAD DATA ... REPLACE INTO TABLE instead of UPDATE table_name SET

I see no benefit on using LOAD DATA when you could use UPDATE table_name SET.

Use LOAD DATA when importing large amounts of data or data from files.

Personally, I have and would never use LOAD DATA for an UPDATE task.