Speed up your PHP

Cache array size calculations

One common programming block in PHP scripts is looping over an array and performing some calculations on the contents. Here’s the stereotypical example:

for ($i = 0; $i < count($items); $i++)
{
  // do something with $items[$i]
}

Whilst there is nothing functionally wrong with the code above, the problem is that it calls the count function for every single loop iteration, i.e. there are count($items) function calls. If you are not changing the size of $items during the loop (modifying the individual elements is fine) then you can cache the result of the count function like so:

$itemCount = count($items);
for ($i = 0; $i < $itemCount; $i++)
{
  // do something with $items[$i]
}

The code still works in exactly the same way, but you’ve removed (count($items) - 1) function calls. This might not make a big difference for small arrays, but if you’re looping over arrays with hundreds or thousands of elements, the effect should be noticeable.

Of course, you could also use foreach to achieve a similar effect, but that construct does not allow you to compare two elements easily (e.g. $items[$i] == $items[$i - 1]).

Filter and sort in SQL

I’ve lost count of the number of PHP scripts where the author selects some information from a database, then proceeds to filter and/or sort the information using PHP. Here’s a simple example which demonstrates both issues (assume $db is a handle to your favourite database abstraction layer):

$sql = "SELECT * FROM table";
$rs = $db->query($sql);
$prices = array();

while ($result = $rs->fetchRow())
{
  if ( $result['price'] > 10 )
  {
    $items[] = $result['price'];
  }
}

array_multisort($prices, SORT_ASC);

Here’s a better way to do this, using SQL:

$sql = "SELECT * FROM table WHERE price > 10 ORDER BY price ASC";
$rs = $db->query($sql);
$prices = $rs->GetRows();

Why should you sort and filter in SQL and not PHP? Firstly, by filtering in SQL you are reducing the size of the result set which is returned by the database query. If your table contains 10,000 rows but only 5 match your filter, that’s a significant saving.

Secondly, no matter how much you optimise your PHP sorting, the authors of your database engine have been doing this for years and will probably have come up with a better result than you have.

The final reason for filtering and sorting in SQL is not related to speed, but to debugging. If your application is producing the wrong result set, you can simply copy and paste the SQL into the command prompt for your database and see what comes out. On the other hand, if you sort and filter in PHP, it will take you a lot longer to work out whether the error is in your SQL or your PHP filtering/sorting.

Of course, it won’t always be possible or desirable to filter and sort in SQL, particularly if you need to fetch rows in such a way that JOINs become unwieldy. However, as a general rule of thumb, if it’s possible to do it in SQL, use SQL over PHP.