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
– wiredniko, Commented Dec 12, 2013 at 15:33
This answer might help: Update multiple rows with multiple 'where' clauses for each individual row — r3mainer
– r3mainer, Commented Dec 12, 2013 at 15:35
Yes. You can speed this up by executing one MySql command that includes the foreach loop. See JOINS — Strawberry
– Strawberry, Commented Dec 12, 2013 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
– Ivo Renkema, Commented Dec 12, 2013 at 15:40

Ryan · Accepted Answer · 2013-12-12 15:38:39Z

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, 2013 at 15:38

Ryan

28.4k10 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan Over a year ago

This of course assumes that it is viable to get all the updates in a separate table in the first place.

Tom McClure Over a year ago

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.

Ivo Renkema Over a year ago

Interesting. Not sure this would work in my actual use-case, which is more complicated than shown. But I will give this a try.

bmccall1 · Accepted Answer · 2013-12-12 15:50:05Z

1

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";

 }

answered Dec 12, 2013 at 15:50

bmccall1

791 gold badge1 silver badge7 bronze badges

Comments

Michael · Accepted Answer · 2013-12-12 15:42:34Z

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, 2013 at 15:42

answered Dec 12, 2013 at 15:35

Michael

12.9k6 gold badges57 silver badges69 bronze badges

2 Comments

Strawberry Over a year ago

We're trying to dispense with the loop (specifically, the round trips to the DB). This ain't helping.

Ivo Renkema Over a year ago

Although this does not dispense with the loop, this might actually be a n important improvement. Thanks,

Collectives™ on Stack Overflow

speed up execution by foreach loop in MySql command

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related