Since this was about comparing MySQL to PHP, I wasn't sure which forum to put it under... so I just chose "General" :-D If the mods think it should be under one of the others, though, I'm OK with that...

I have a script that's pretty heavy on the MySQL. I run 2 queries (times taken at around 9:30pm, so not during peak but still heavy traffic):

// 0.0115
SELECT classifieds.id, username, price, classifieds.expiration, title, classifieds_img.image, description
FROM classifieds
JOIN classifieds_county
ON classifieds_county.id = classifieds.id
LEFT JOIN classifieds_img
ON classifieds_img.id = classifieds.id
WHERE 
(
(
category= '%s'
AND subcat= '%s'
)

# Not the same as above, uses different values for %s
OR (category = '%s' AND subcat = '%s')
)

AND status = '%s'
AND classifieds.expiration >= %s
AND classifieds_county.county='%s'
AND COALESCE(classifieds_img.sorter,0) = 0
ORDER BY id DESC

// 0.0678
SELECT username, COUNT(1) AS nums
FROM classifieds
INNER JOIN classifieds_county
ON classifieds_county.id = classifieds.id 
WHERE classifieds_county.county='%s' AND 
status = '%s' AND 
classifieds.expiration >= %s
GROUP BY username

Then I loop through all of the second query (about 8000 results) and add each row to an associative array (eg, $count[$username] = $num). Then I loop through the first query (anywhere from 100 to 2000 results) and push each row of results to another array. Then I run through a third loop (the same length as the loop around the first query, 100 to 2000) to format and echo the result to the page, including the aforementioned $count[$username].

(I'm adding them to an array because I'm also comparing an optional "price" field as a number, but they're not all entered as a number so it was easier to use preg_replace and find the numeric part of the entered price; there's another thread under MySQL if you're interested in my logic on that)

So, two queries and three while() loops through the results.

As an alternative, I realize that I could do 1 query, eliminating part of the WHERE:

// 0.0329; not sure why this is slower than the first query with more WHERE
// statements, maybe just a server load fluke?
SELECT classifieds.id, username, category, subcat, price, classifieds.expiration, title, classifieds_img.image, description
FROM classifieds
JOIN classifieds_county
ON classifieds_county.id = classifieds.id
LEFT JOIN classifieds_img
ON classifieds_img.id = classifieds.id
WHERE status = '%s'
AND classifieds.expiration >= %s
AND classifieds_county.county='%s'
AND COALESCE(classifieds_img.sorter,0) = 0
ORDER BY id DESC

Then I could loop through that in PHP (about 8000 results), count each username manually, then in PHP check to see if the old WHERE values match and, if so, push it to the array. Something like:

while ($row = mysqli_fetch_row($sth)) {
 list($id, $username, $price, $expiration, $title, $img, $desc = $row;

 if ($count[$username]) $count[$username]++;
 else $count[$username] = 1;

 // showing %s to match the first query, but this would really be $_GET['whatever']
 if ($category == '%s' && $subcat == '%s')
  array_push($classifieds, $row);
}

while (list($id, $username, $price, $expiration, $title, $img, $desc) = $row) {
 // format and echo
}

So, one query that loads in less than half the time of the other two queries combined, and two while() loops, but more PHP work inside of the first loop.

Which option do you think would be better, in terms of page load time, server load, and overall for the end user? The second option would require a bit of a rebuild on my end, but if it's significantly better then maybe it's worth it.