Best way to do this UPDATE query

Question

I have to do a update query of 800k rows and looking for the best way to do this. All rows are updated with the same values excepted one field (D in my exemple). This field can be 1 or 0. I use update() methode of Zend_Db. I think about 3 methods to do this :

Methode 1 : Update each row, one after one (with a foreach).
Methode 2 : Do an IF in the update to set the value of the field
Methode 3 : Divide rows in two groups (one with field = 1 and another with field = 0) and make two updates (UPDATE ... WHERE id IN (...)), one for each group.

Query looks like this :

$a_data = array(
  'A' => foo,
  'B' => 99,
  'C' => 0,
  'D' => (0 OR 1 ?)
);

$where['id IN (?)'] = $a_id;
$update = $this->_db->update($this->_name, $a_data, $where);

Witch method can be the best way to do this ? Thanks

FYI : A discussion about the speed of PHP vs MySQL http://stackoverflow.com/questions/13176469/php-vs-mysql-performance-if-functions-in-query — AgeDeO, Feb 18 '15 at 14:11
There is nothing in your sample code to indicate how you decide which rows get set to `D = 1` or `D = 0`. That might help with a solution. — RiggsFolly, Feb 18 '15 at 14:17

score 1 · Accepted Answer · answered Feb 18 '15 at 14:19

For the record, 800k rows updated on a live production server isn't a good plan. Except being done at an actual mysql level, the chances of this update stopping your server are high.

Now, that being said, and assuming you're running MySql,

Method 1. isn't feasible if for nothing else than that you have 800k rows => 800k queries. max_timeout in php.ini will not allow for the script to run that long. If you still want to try it, try splicing the results into batches of 50-100-200 (depending on your server configuration) and run each batch with a time difference between them. Do a batch, wait a second, do a batch, wait a second, and so on...

Method 2. i guess it pertains to your certain problem, but it will be quicker.

Method 3. see answer for Method 1, except it's not 800k at once, but depends on the ratio between your 0 and 1's. It's going to be 2 queries each pretty large.

Usually, when there's a large batch update like this, I'd say, use mysql from a command line. If this is an update php script that you're running, the best results are splicing the results and updating 50-100-whatever number at a time. Although it's time consuming (800.000rows / 100rows at a time = 800 runs of the script + a pause of a second after every updated batch).

Best way to do this UPDATE query

1 Answers1