Mysql – What’s the best way to update several (200k)rows in a database

MySQL

I've got a database that I need to update a column in some rows. I've got the data from my logs and I wrote php script that processes each log and extracts the data in to a file with an update for each record that needs it.

I broke it down in to each month but running this is taking forever. Is there a better way to do it?

The file is basically a .sql file filled with 205k rows of

update table set ended_at = '2019-01-31 23:59:30' where uuid = '304ad6ea-b949-42f5-96a0-a7a550e6126d';

Is there a better way?

Best Answer

I would suggest that the schema is poorly designed if you have 205K rows with the identical uuid.

Furthermore, if you need all of them to have the same ended_at value, then there should be some other table that has, for example, one row with uuid and ended_at. The JOIN to get the data during a SELECT will be more than compensated for by having removed ended_at from the big table.

Perhaps I am being overly critical. If so, please provide some more insight into what the columns mean, and where the uuids come from, etc.

The best way to update "many" rows in a "huge" table is to walk through the table by the PRIMARY KEY, and update a thousand rows at a time. More details on chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Related Solutions

MySQL binary logs – skip a table

There is no mysql option to ignore a table in binary log.

If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules.

If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line interface to LOAD DATA INFILE), and the user you are importing the data with can have the SUPER privilege you might use sql_log_bin session variable like:

    SET @@session.sql_log_bin=0;
    LOAD DATA INFILE 'file_name' 
      -- other LOAD DATA INFILE options ;
    SET @@session.sql_log_bin=1;

This will disable binary logging for the current session, load the data and enable binary logging again.

Mysql – Easiest way to duplicate rows

This is a very simple INSERT .. SELECT query.

The id is not included in the column list as it has the AUTO_INCREMENT property and will get values automatically. You only need to replace 'aaa' with the menucardhash value that you want to duplicate from and 'qqq' with the new value:

INSERT INTO menuship3
    (headhash, menucardhash, menucathash, producthash)
SELECT 
    headhash, 'qqq', menucathash, producthash
FROM 
    menuship3
WHERE 
    menucardhash = 'aaa' ;

Best Answer

Related Solutions

MySQL binary logs – skip a table

Mysql – Easiest way to duplicate rows

Related Question