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

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:

• Exporting the existing table to a CSV (text) file.

• Modifying the corresponding row(s) in the CSV file.

• 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.

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.

StackExchange Database Administrators Q#317710, answer score: 4