Things you should not do in PHP (update: references)

Here is a list of things you should not do in PHP. Most of the stuff is pretty obvious, but over the years I've seen a lot of them. In most cases, these problems remain hidden until data grows above 10000 entries. So on a development system, things are always fast and there are no problems with memory limits :-)

Suppose we have a table with 100k entries:

$db->query('create table stats (c1 int(11) primary key, c2 varchar(255))');
$db->query('begin');
for ($i=0; $i<100000; $i++) {
  $db->query('insert into stats values ('.$i.','.($i*2).')');
}
$db->query('commit');

Populate a big array instead of streaming results:

$result = $db->query('select * from stats');
$array = $result->fetch_all(); // 35M
// or
while ($row = $result->fetch_assoc()) $array[] = $row; // 35M
// or
while ($row = $result->fetch_array()) $array[] = $row; // 44.5M
// process $array ...

// instead of:
while ($row = $result->fetch_assoc()) { // 0.5M
  // process $row
}

Sum with PHP instead of SQL:

$sum = 0;
foreach ($array as $val) $sum += $val[0]; // 44M, 1.2s

// instead of:
list($sum,) = $db->query('select sum(t1) from stats')->fetch_row(); // 0.2M, 0.1s

Sort with PHP instead of SQL:

usort($array, function ($a, $b) { return $a[0] > $b[0]; }); // 4.1s
// or
foreach ($array as $key=>$val) $helper[$key] = $val[0];
asort($helper); // 2.2s

// instead of:
$result = $db->query('select * from stats order by c1');
while ($row = $result->fetch_assoc()) { // 1.2s

Let's add a second table:

$db->query('create table stats2 (c1 int(11) primary key, c2 varchar(255))');
$db->query('begin');
for ($i=50000; $i<51000; $i++) {
  $db->query('insert into stats2 values ('.$i.','.($i*2).')');
}
$db->query('commit');

Join with PHP instead of SQL (join result contains 1000 entries):

$array = $db->query("select * from stats")->fetch_all();
$array2 = $db->query("select * from stats2")->fetch_all();

foreach ($array as $key=>$val) {
  foreach ($array2 as $key2=>$val2) { // 35.7M, 69s
    if ($val[0] == $val2[0]) // do sth.
  }
}

// instead of:
$result = $db->query('select * from stats a, stats2 b where a.t1=b.t1');
while ($row = $result->fetch_array()) { // 0.5M, 0.015s

Modify arrays without references:

$array = array();
for ($i=0; $i<1000000; $i++) $array[] = $i*2;

$start = microtime(true);
foreach ($array as &$val) $val++;
echo (memory_get_peak_usage(true)/1048576)."\n"; // 80M (32bit), 200M (64bit)
echo (microtime(true)-$start)."\n"; // 0.14s

$start = microtime(true);
foreach ($array as $key=>$val) $array[$key]++;
echo (memory_get_peak_usage(true)/1048576)."\n"; // 161M (32bit), 399M (64bit)
echo (microtime(true)-$start)."\n"; // 0.64s

more examples coming ...

Scripts running on a 1.4 GHz machine with PHP 5.4.0.