How to update millions of rows quickly in mysql

Question

I need to update over 4 million rows in my database. We are currently using mysql 5.1 and the updates are very slow. At the moment it is taking over 30 minutes for less than 100,000 rows, which is undesirable.

I have an sql script which autogenerates the update statements which I need and outputs them to a file. I have tried breaking this out into multiple files, to see if this speeds things up, but to no avail.

My script uses a select concat to generate the update statements and write them to a file as follows:

 SELECT CONCAT("UPDATE status SET next_status_id=", ts_next.id, ",duration=",
 SUM(UNIX_TIMESTAMP(ts_next.status_date) - UNIX_TIMESTAMP(ts.status_date)),
  " WHERE id =", ts.id, " AND next_status_id IS NULL AND duration = 0;")
 into outfile '/tmp/status_updates.sql'
 FIELDS TERMINATED BY '' 
 LINES TERMINATED BY '\n'
FROM
   status ts
        LEFT JOIN
      status ts_next ON ts_next.ticket_id = ts.ticket_id
        AND ts_next.id = (SELECT 
            MIN(id)
        FROM
            status first_status
        WHERE
            first_status.id > ts.id AND first_status.ticket_id = ts.ticket_id)
            GROUP BY ts.id;

Ideally, I would like to get these updates done as quickly as possible, and am open to all suggestions for the best way to do this with minimal impact.

SOLUTION:

UPDATE status ts1,
    (SELECT 
    ts_next.id as next_status_id,
    ts.id as status_id,
    IFNULL(SUM(UNIX_TIMESTAMP(ts_next.status_date) - UNIX_TIMESTAMP(ts.status_date)), 0) as duration
    FROM
   status ts
        LEFT JOIN
      status ts_next ON ts_next.ticket_id = ts.ticket_id
        AND ts_next.id = (SELECT 
            MIN(id)
        FROM
           status first_status
        WHERE
            first_status.id > ts.id AND first_status.ticket_id = ts.ticket_id)
            GROUP BY ts.id) ts2
    SET ts1.next_status_id = ts2.next_status_id, ts1.duration = ts2.duration 
    WHERE id=ts2.status_id AND ts1.next_status_id IS NULL;

Answer 1

Use an "Update-Select". I think it should be the fastest way to update many rows. Therefore see this question: MySQL - UPDATE query based on SELECT Query