CODEWITHSUNDEEP
phpmysqlperformance
DroidOS
DroidOS

Reputation: 8890

Pointless SQL vs PHP if

I need to manipulate entries in a mySQL table using code like this

foreach($items as $item)
{
 $sql = "UPDATE `siteims` SET refs = refs + 1 WHERE imid = '{$item->img}';";
 $sql .= "UPDATE `lists` SET refs = refs + 1 WHERE lid = '{$item->lili}'";
 $dbh->exec($sql);
}

There may be as many as 50 items in $items. A variation on this code would be

foreach($items as $item)
{
 if ('z' != $img->img)
 $sql = "UPDATE `siteims` SET refs = refs + 1 WHERE imid = '{$item->img}';";
 if ('z' != $item->lili)
 $sql .= "UPDATE `lists` SET refs = refs + 1 WHERE lid = '{$item->lili}'";
 $dbh->exec($sql);
}

In both cases I am executing a sequence of SQL statements for EACH item in $items. My questions

  1. Would it not be a whole lot more efficient to build $sql for items and then execute it?
  2. But then if all of the, potentially, 50 items in $items produces meaningful SQL would that not mean a very slowly executing batch of SQL statements?
  3. Finally, is it better to perform PHP side if tests as in the second version of my code or just build the SQL and let mySQL deal with the fact that the WHERE test returns an empty row?

I'd much appreciate any help with this.

Upvotes: 0

Views: 147

Answers (2)

Svetoslav
Svetoslav

Reputation: 4686

At first place I advice you to use IN clause and just 2 separated queries... You may have to escape those 2 elements $item->img and $item->lili .. 

$ids = array("siteims"=>array(), "lists"=>array());
foreach($items as $item)
{
    $ids['siteims'][] = "'" . $item->img . "'";
    $ids['lists'][] = "'" . $item->lili . "'" ;
}
if(!empty($ids['siteims'])){
    $sql = "UPDATE `siteims` SET refs = refs + 1 WHERE imid IN (".implode(',', $ids["siteims"]).")";
    $dbh->exec($sql);
}

if(!empty($ids['lists'])){
    $sql = "UPDATE `lists` SET refs = refs + 1 WHERE lid IN (".implode(',', $ids["lists"]).")";
    $dbh->exec($sql);
}

Upvotes: 1

Marc B
Marc B

Reputation: 360662

You could use an in clause, instead, e.g.

$sql = "UPDATE .... WHERE imid IN (" . implode($array_that_has_the_ids) . ")"

and reduce yourself down to just one single SQL query. However, this can fail if you're trying to use a HUGE aray - the generated query could exceed the max_allowed_packet setting and get killed.

As for your strlen... what's the point of comparing strlen results against 'z'? strlen returns an integer, you might as well be doing if (apple == orange) instead.

Upvotes: 3

Related Questions