Search code examples
mysqlsql-updateload-data-infile

How to UPDATE many rows(1 500 000) fast

I have table with 1.5 mil rows and I have 47k values to update. I've tried two ways of doing it and both are pretty slow.

First is 47k rows of

UPDATE $table 
SET name = '$name' 
WHERE id = '$id'

Second is

$prefix = "UPDATE table
    SET name = (case ";
while () {
    $mid .=  "when id = '$id' then '$name' ";   
}
$suffix = "end);";

$query = $prefix . $mid . $suffix;

Is there a way of doing it faster? Maybe with LOAD DATA INFILE ? Can't figure out the UPDATE syntax with this one.

Solution

  • I had to import large files on a daily basis, and tried all sorts of things.

    In the end I got the best performance a specific combination of:

    1. First copy the CSV to the database server, and load it from the local disk there, instead of loading the CSV from your client machine.
    2. Make sure that you have a table structure that matches exactly with this. I've used a temporary table for the import, and then used separate queries on that to get data into the final table.
    3. No foreign keys and unique index checks on the tmp table.
    4. That will speeds things up a lot already. If you need to squeeze more performance in, you can increase the log buffer size.

    And obviously:

    • make sure that you don't import stuff that you don't need to. Be critical about which fields you include, and which rows.
    • If you only have a few different values of text in a column, use a numeric value for it instead.
    • Do you really need 8 decimals in your floats?
    • Are you repeatedly importing the same data, where you could insert updates only?
    • Make sure that you don't trigger unnecessary type conversions during import. Prepare your data to be as close to the table that you're importing into.