Outputting results from a conditional row count across multiple tables

Question

Follow up question for: Conditional row count across 4 tables
After getting some excellent help from 200_success, i managed to output the SUM() total of my query using the following code:

get_results returns standard mysqli_assoc_array:

$row = $this->get_results($query);
$sum = $row[0];
 foreach($sum as $sum) {
    echo $sum;
}

Is this the best way to handle the result, are there any drawbacks / performance hits to doing it this way, how would you handle this querys result?

Query

$query = "SELECT SUM(inactive_count) AS inactive_count
FROM (
    SELECT COUNT(article_active) AS inactive_count
        FROM wcx_articles WHERE article_active = 0
    UNION ALL
    SELECT COUNT(video_active)
        FROM wcx_videos WHERE video_active = 0
    UNION ALL
    SELECT COUNT(app_active)
        FROM wcx_apps WHERE app_active = 0
    UNION ALL
    SELECT COUNT(link_active)
        FROM wcx_links WHERE link_active = 0
) AS wcx_objects";

Answer 1

MySQL

Your query looks fine, I'm not sure it could be further optimized of itself. What I would suggest though is to plug it into a stored procedure, and there are a few good reasons to do this.

1. Cleaner PHP script by only having your behavioral code in your script while just calling the procedure from there.

2. Easier to modify, e.g., if you found yourself using this SQL script in multiple PHP scripts and you had to make a change to it, you would have to change it everywhere, as opposed to just changing the stored procedure once.

3. Better SQL optimization. The way you have it now, every time you run the PHP script it sends an ad-hoc request to the SQL engine and it has to figure out the optimization each time. By storing the SQL script in the RDBMS the engine will optimize it ahead of time, so it will respond quicker each time.

Create procedure

You can run this just once:

DELIMITER |    
CREATE PROCEDURE wcx_objects() AS    
BEGIN    

SELECT SUM(inactive_count) AS inactive_count
FROM (    
    SELECT COUNT(article_active) AS inactive_count    
        FROM wcx_articles WHERE article_active = 0    
    UNION ALL    
    SELECT COUNT(video_active)    
        FROM wcx_videos WHERE video_active = 0    
    UNION ALL    
    SELECT COUNT(app_active)    
        FROM wcx_apps WHERE app_active = 0    
    UNION ALL    
    SELECT COUNT(link_active)    
        FROM wcx_links WHERE link_active = 0    
) AS wcx_objects;    

END|    
DELIMITER ;

Call Procedure in PHP

Then from PHP all you need to do to get your result set is write this:

$query = "CALL wcx_objects();"