3 Replies - 3860 Views - Last Post: 29 July 2012 - 02:05 AM

#1 izrafel  Icon User is offline

  • D.I.C Head

Reputation: 11
  • View blog
  • Posts: 96
  • Joined: 24-July 09

multiple row update in one query, or cycle queries?

Posted 28 July 2012 - 12:46 AM

ok here is the situation, i have a some pictures, not more than 10-20, and the user can set weights so he can order them, but here comes the problem, i have two options
1.(standard)
foreach($images as $k=>$v)
{
 //each image we do an update query
$q="update `images` SET `orderby`=".$v." WHERE `idimage`=".$k;
//execute $q 
}


2. (more interesting)
$q="UPDATE `images` SET `orderby`= CASE `idimage` ";
$ids=implode(',',array_keys($images));
foreach($image as $k=>$v){
$q.=' WHEN '.$k.' THEN '.$v;
}
$q.=' END WHERE `idimage` IN('.$ids.')';
//execute $q


So which do you think is better?when i try to do explain in the second case i get an error, that the query has an error.
Not quite sure, but i think that the second case is better , so i decided to ask the mysql gurus :)

Is This A Good Question/Topic? 0
  • +

Replies To: multiple row update in one query, or cycle queries?

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: multiple row update in one query, or cycle queries?

Posted 28 July 2012 - 02:21 AM

Hey.

I'm assuming this is not a frequent operation? Like, this will not happen for every user that uses the application, every few seconds? - Does optimizing the performance of this query matter a great deal? Is this some sort of a bottleneck? If not, then I would advice you to use the simpler code. You really should avoid increasing the complexity of the code/query unless it's needed.

Which query would be faster I'm not entire sure. With only 20 rows I can't imagine either query taking more than a couple of milliseconds to execute. You'd have to benchmark them on much larger data sets to find any signification difference. (Actually, I might do that later, just out of curiosity. I'll post back if I do.)

izrafel said:

when i try to do explain in the second case i get an error, that the query has an error.

For MySQL versions earlier than 5.6.3, the EXPLAIN statement only works on SELECT queries. (Ref.)
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: multiple row update in one query, or cycle queries?

Posted 28 July 2012 - 07:44 AM

I made a simple test to see the performance difference, and according to that your second method is around 20 times (InnoDB) or 5 times (MyISAM) faster than the first method :) - Even so, we're talking about execution times in milliseconds here, so it's not exactly an issue either way until you start executing it multiple times per second.

I'll post the details of my test here if you're interested.

Spoiler

Was This Post Helpful? 0
  • +
  • -

#4 izrafel  Icon User is offline

  • D.I.C Head

Reputation: 11
  • View blog
  • Posts: 96
  • Joined: 24-July 09

Re: multiple row update in one query, or cycle queries?

Posted 29 July 2012 - 02:05 AM

yeah thought so :), the table will be relatively big(10 000 - 100 000 rows), but the users are limited to 10-20 max images.so they can't execute more than 10-20 queries. and this specific query will not be executed very frequently this just changes the order weights field used to order the images the way the user wants.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1