speed up execution by foreach loop in MySql command

Question

I have a PHP foreach statement, looping through a large number of $icons.

For each icon, the DB-column sequence needs to be updated. As follows:

foreach ($icons as $key => $icon) {
    // MySql pseudo-code:
    UPDATE `tbl_icon2album` 
    SET `sequence`= $key +1 
    WHERE iconID= $icon['id']       
}

My problem: this becomes very slow for a large number of icons.

My question: Can I speed this up by executing one MySql command that would somehow include the foreach loop?

Much obliged...

What are you trying to accomplish? Are you updating the icon sequence every time a new icon is being added? — wiredniko, Dec 12 '13 at 15:33
This answer might help: [Update multiple rows with multiple 'where' clauses for each individual row](http://stackoverflow.com/questions/15344120/update-multiple-rows-with-multiple-where-clauses-for-each-individual-row) — r3mainer, Dec 12 '13 at 15:35
Yes. You can speed this up by executing one MySql command that includes the foreach loop. See JOINS — Strawberry, Dec 12 '13 at 15:35
@Strawberry, What do you mean by see JOINS? JOINS as in joining a table or another query? If so, how? — Ivo Renkema, Dec 12 '13 at 15:40
@Tom McClure. $key is simply the index from the foreach loop. — Ivo Renkema, Dec 12 '13 at 15:45

score 3 · Answer 1 · answered Dec 12 '13 at 15:38

3

You could put all your updates in another table, and update using a single query, e.g.

 UPDATE tbl_icon2album, some_other_table
 SET    sequence = some_other_table.new_key_value
 WHERE  iconID = some_other_table.icon_reference

answered Dec 12 '13 at 15:38

Ryan

26,884
9
56
83

This of course assumes that it is viable to get all the updates in a separate table in the first place. – Ryan Dec 12 '13 at 15:41
1

It's viable - I assume only the relative order of the key values is important, so just do a mass insert statement to a temp table with an autoincrement key, and then the icons will be in the correct sequence. Finally, run this UPDATE and drop the temp table. – Tom McClure Dec 12 '13 at 15:48
Interesting. Not sure this would work in my actual use-case, which is more complicated than shown. But I will give this a try. – Ivo Renkema Dec 12 '13 at 16:54

score 1 · Answer 2 · answered Dec 12 '13 at 15:50

How many keys are you updating? Is it the iteration that is slow, or are you doing this thousands of times?

You could use the "in" clause.

ie:

 update table set key=key+1 where blah in ('1','2','3');

and you could iterate through the for loop to construct a variable passed to in:

ie:

 $iconlist = "";
 foreach ($icons as $key => $icon) {
   if (!$iconlist) { $iconlist = "($icon" }
   else 
   { $iconlist .= ",$icon" }
 }
 if ($iconlist) { 
   $iconlist .= ")";
   $query = "update table set key=key+1 where icon in $iconlist";

 }

Michael · Answer 3 · 2013-12-12T15:42:34.057

0

If you use prepared statements then you can prepare the query once, bind the parameters, and then execute within the loop. This could be faster as it is usually preparing the query that takes up time. For example:

$stmt = $mysqli->prepare("

  UPDATE
    `tbl_icon2album`
  SET
    `sequence` = ?
  WHERE
    `iconID` = ?

");

$stmt->bind_param('ii', $sequence, $icon_id);

foreach ($icons as $key => $icon)
{

  $sequence = $key + 1;

  $icon_id = $icon['id'];

  $stmt->execute(); 

}

edited Dec 12 '13 at 15:42

answered Dec 12 '13 at 15:35

Michael

11,912
6
49
64

We're trying to dispense with the loop (specifically, the round trips to the DB). This ain't helping. – Strawberry Dec 12 '13 at 16:06
Although this does not dispense with the loop, this might actually be a n important improvement. Thanks, – Ivo Renkema Dec 12 '13 at 16:54

speed up execution by foreach loop in MySql command

3 Answers3