Put the Load on PHP or on MySQL Server?

[Caligatio]

I have a general question to ask those whom are more knowledgable in this area than I:

Although nothing I'm doing right now is all that intensive (biggest table has like 700 rows) I was curious to know if it's better to make MySQL crunch numbers or PHP. Example as follows:

SELECT member.netid, member.fname, member.lname, status.excused, status.unexcused, status.pending, strikes.modstrikes FROM member LEFT JOIN ((SELECT netid, COUNT(CASE WHEN status = 'excused' THEN 1 ELSE NULL END) as excused, COUNT(CASE WHEN status = 'unexcused' THEN 1 ELSE NULL END) as unexcused, COUNT(CASE WHEN status = 'pending' THEN 1 ELSE NULL END) as pending FROM `attendance` GROUP BY netid) as status) ON member.netid = status.netid LEFT JOIN strikes ON status.netid=strikes.netid WHERE (member.status='active' OR member.status='neophyte') AND member.print='Y' ORDER BY member.lname

Previously I had:

$sql = "SELECT netid, status, event FROM attendance";
$res = mysql_query($sql);

while ($arr = mysql_fetch_array($res)) {
$absences[$arr[netid]][] = $arr[status];
}

$sql = "SELECT member.netid, member.lname, member.fname, strikes.modstrikes FROM member LEFT JOIN strikes ON member.netid = strikes.netid WHERE (member.status = 'active' or member.status='neophyte') AND member.print = 'Y' ORDER BY member.lname";
$res = mysql_query($sql);

while ($arr = mysql_fetch_array($res)) {
$excused = 0;
$unexcused = 0;
$madeup = 0;
foreach ($absences[$arr[netid]] as $temp) {
switch($temp) { case Excused: $excused++;
break;
case Unexcused: $unexcused++;
break;
case Madeup: $madeup++;
break;
}
}

Ignoring the benefits of the double LEFT JOIN, is making MySQL doing the count better or is making the PHP?

[Dabrowski]

Is making MySQL doing the count better or is making the PHP?

I'm sure this won't help, but it might be fun to check the execute times. I don't know exactly how to do it, but I've seen the "This page loaded in .0039 seconds" notes around. Maybe you could do a little experiment:)

Sorry. I don't have an actual answer to the question:(

[B&T]

In general you are better off to make the MySQL as specific as possible so that it returns the least rows possible rather than have MySQL return extra rows and further process it in PHP. If that is your question . . .

[Caligatio]

B&T,

The queries return the same number of rows but varying colums. I'm not sure if that makes a difference or not.

[B&T]

B&T,

The queries return the same number of rows but varying colums. I'm not sure if that makes a difference or not.
Same answer - let MySQL do the work if you can.

[Caligatio]

Oh sorry, I misread your post. Yea, I figure MySQL taking the load would be better but was not positive.