[izrafel]

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

[Atli]

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.)

[Atli]

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

I'll just put up the code for the tests themselves here. The rest of what is needed to make them run is in the zip.

 update_perf.zip (4.15K)
Number of downloads: 18

The specs of the PC I ran this on, by the way, are:
MySQL 5.5.15, PHP 5.4.4 (using PDO), Windows 7 (x64).

class StandardTest implements TestInterface
{
    public function execute($updates)
    {
        $ids = createRandomIDs(500000, 0, $updates);
        $updated = 0;
        
        $start = microtime(true);
        
        $sql = "UPDATE `utest` SET `value` = ? WHERE `id` = ?";
        $stmt = Database::get()->prepare($sql);
        $params = [null,  null];
        foreach ($ids as $id) {
            $params[0] = createRandomString();
            $params[1] = $id;
            $stmt->execute($params);
            if ($stmt->rowCount() == 1) {
                ++$updated;
            }
        }
        
        if ($updated !== $updates) {
            throw new Exception("StandardTest::execute - Number of actual updates " .
                                "does not match the requested amount: {$updated}/{$updates}");
        }
        
        return microtime(true) - $start;
    }
}

class ComplexTest implements TestInterface
{
    public function execute($updates)
    {
        $ids = createRandomIDs(500000, 0, $updates);
        $start = microtime(true);
        
        $sql = "UPDATE `utest` SET `value`= CASE `id` \n";
        foreach ($ids as $id) {
            $sql .= "    WHEN " . $id . " THEN '" . createRandomString() . "'\n";
        }
        $sql .= " END \nWHERE `id` IN(" . implode(",", $ids)  . ")";
        
        $updated = Database::get()->exec($sql);
        if ($updated !== $updates) {
            throw new Exception("ComplexTest::execute - Number of actual updates " .
                                "does not match the requested amount: {$updated}/{$updates}");
        }
        
        return microtime(true) - $start;
    }
}

[izrafel]

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.