Is it better to put the load on MySQL or PHP?

[csdude55]

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.

[Peter_S]

The best is simply to time both version, and see the one which is the fastest. Even if PHP is interpreted language, sometimes it can be faster than binary code application (like MySQL), especially if you are using PHP 7.

All really depends of the nature of the processing. So, just time both code and see which one performs the faster.

$time_start = microtime ( true ) ; 

/.../

$time_end = microtime ( true ) ;

$execution_time = ( $time_end - $time_start ) ;

Excepting if your whole PHP script is more complex, I wouldn't call the "list" function to retrieve variables. I would simply use $row [ 'id' ], $row [ 'uername'] etc... especially if one day your sql statement is change, you might not end with the right values.

Also, instead of:

array_push($classifieds, $row);

You can do

$classifieds [ ] = $row ;

This will be faster to use a language operator than calling a function.

[csdude55]

I probably will build both and compare, I was just hoping someone might have been-there-done-that and could save me the stress ;-) I'll probably build it over the weekend, and if so I'll post back with my results.

Thanks for the tip regarding array_push, I never really thought of it like that! And regarding the list()... that's a good point, too. My script is a bit more complex than I posted here, of course, but that might still be a smarter long-term method.