PHP MySQL - Update 6.5m rows performance issues

Question

I am working with a MySQL table and I need to increment a value in one column for each row, of which there are over 6.5m.

The col type is varchar and can contain an integer or a string (i.e. +1). The table type is MyISAM.

I have attempted this with PHP:

  $adjust_by = 1;
  foreach ($options as $option) {
      $original_turnaround = $option['turnaround'];
      $adjusted_turnaround = $option['turnaround'];

      if (preg_match('/\+/i', $original_turnaround)) {
        $tmp = intval($original_turnaround);
        $tmp += $adjust_by;
        $adjusted_turnaround = '+'.$tmp;
      } else {
        $adjusted_turnaround += $adjust_by;
      }

      if (!array_key_exists($option['optionid'], $adjusted)) {
        $adjusted[$option['optionid']] = array();
      }

      $adjusted[$option['optionid']][] = array(
        'original_turn' => $original_turnaround,
        'adjusted_turn' => $adjusted_turnaround
      );
  }//end fe options

  //update turnarounds:
  if (!empty($adjusted)) {
    foreach ($adjusted as $opt_id => $turnarounds) {
      foreach ($turnarounds as $turn) {
        $update = "UPDATE options SET turnaround = '".$turn['adjusted_turn']."' WHERE optionid = '".$opt_id."' and turnaround = '".$turn['original_turn']."'";
        run_query($update);
      }
    }
  }

For obvious reasons there are serious performance issues with this approach. Running this in my local dev environment leads to numerous errors and eventually the server crashing.

Another thing I need to consider is when this is run in a production environment. This is for an ecommerce store, and I cannot have a huge update like this lock the database or cause any other issues.

One possible solution I have found is this: Fastest way to update 120 Million records

But creating another table comes with it's own issues. The codebase is not in a good state, similar queries are run on this table in loads of places so I would have to modify a large number of queries and files to make this approach work.

What are my options (if there are any)?

Answer 1

You can do this task with SQL.

• With CAST you can convert a string into integer.
• With IF and SUBSTR you can check if string contains +.
• With CONCAT you will add (merge a two values into one string) + to your calculated result (if it will be necessary).

Just try this SQL:

"UPDATE `options` SET `turnaround` = CONCAT(IF(SUBSTR(`turnaround`, 1, 1) = '+', '+', ''), CAST(`turnaround` AS SIGNED) + " + $adjust_by + ") WHERE 1";

Answer 2

You probably don't have, but need, this 'composite' index (in either order):

INDEX(optionid, turnaround)

Please provide SHOW CREATE TABLE.

Another, slight, performance boost is to explicitly LOCK TABLE WRITE before that update loop. And UNLOCK afterwards. Caution: This only applies to MyISAM.

You would be much better off with InnoDB.

Answer 3

can't you just say

UPDATE whatevertable SET whatever = whatever + 1?

Try it and see, I'm pretty sure it will work!

EDIT: You have strings OR integers? Your DB design is flawed, this probably won't work, but would have been the correct answer had your DB design been more strict.