Is mergeing of these (long) mysql queries possible?

Question

Okay, my dilemma is this. I have an admin page that I use to get a summary of the day's submissions to one of my sites. This sumamry page pulls data from several tables, some of which store the actual media data, others store the website urls, ids and owners, and another table stores the day-to-day stats to calculate in/out ratios for each domain.

This admin page has a main query, which pulls the actual media data, which looks like this (simplified):

SELECT 
 content.con_catid,
 content.con_posttime,
 content.con_url,
 content.con_id,
 content.con_title,
 categories.name, 
 users.username, 
 users.user_id, 
  FROM content
  LEFT JOIN categories 
  ON (content.con_catid = categories.id) 
  LEFT JOIN users
  ON (content.con_poster = users.user_id)  
  WHERE con_status = 0 ORDER BY con_posttime

Fairly straight forward. Then, this is where it gets messy. For each time it loops the mysql_fetch_array statement (to output each content item), I also run these 2 queries:

SELECT count(con_id) as pending FROM content WHERE con_poster = '$this_userid' AND con_status = 0

SELECT count(con_id) as approved FROM content WHERE con_poster = '$this_userid' AND con_status = 2

These get the number of pending, and approved items for each user who owns the submission (which is listed next to each submission.

And then there is the killer, which adds a lot of time to page execution time, since it also has to be repeated for each row of the initial query.

SELECT website_url, 
website_id, 
website_shady, 
COALESCE(sum(dstats_hits),0)+website_in_ballast as total_in, 
COALESCE(sum(dstats_hits_out),0)+website_out_ballast as total_out, 
sum(dstats_hits_out_paid) as total_paid, 
website_in_ballast, 
website_out_ballast 
  FROM websites 
    LEFT JOIN domain_stats ON (websites.website_id = domain_stats.dstats_domid) 
    WHERE website_id IN (SELECT website_id FROM websites WHERE website_userid = $this_userid)
    GROUP BY website_url

Is there any way I can merge the latter 3 queries int he first one? Because now.... if I have 100 items in the queue, thats 301 queries (401 if you include the sub-query), which take a while to generate the page.

Answer 1

I haven't wrapped my mind around the third additional query yet, but for the first two, you may be able to do something like:

SELECT count(con_id) as pending, con_poster FROM content 
WHERE con_status = 0
GROUP BY con_poster

and then join them against the first query on con_poster.

Answer 2

This feature came in handy for me, maybe it will help you too. You can use a query as one of your selects.

SELECT 
    col_1,
    col_2,
    col_3,
    (
     SELECT col_1
     FROM table_2
     WHERE table_2.col_2 = table_1.col_1
    ) as 'col4',
    col_5
FROM
    table_1

Perfectly legal.

Answer 3

Hmm... the first query simply selects content items, while other queries select some statistics about them. The first query doesn't use any aggregate functions while all others do. Thus the only way I can see for you to join them is to use subqueries. It won't make the execution plan much better, but it will save you a couple hundred query parsings and resultset generations, so it should be a somewhat faster.

One tip though - the middle two queries can be joined together like this:

SELECT
    count(case when con_status=0 then 1 else null end) as pending,
    count(case when con_status=2 then 1 else null end) as approved
FROM
    content
WHERE
    con_poster = '$this_userid'

Answer 4

Joining in content one more time for the specific user_id will give you the possibility to count aswell.

SELECT 
        content.con_catid,
        content.con_posttime,
        content.con_url,
        content.con_id,
        content.con_title,
        categories.name, 
        users.username, 
        users.user_id, 
        IFNULL(SUM(CASE WHEN c2.con_status = 0 THEN 1 ELSE 0 END), 0) as pending,
        IFNULL(SUM(CASE WHEN c2.con_status = 2 THEN 1 ELSE 0 END), 0) as approved
    FROM content
    LEFT JOIN categories 
       ON (content.con_catid = categories.id) 
    LEFT JOIN users
       ON (content.con_poster = users.user_id)  
    LEFT JOIN content as c2 ON users.user_id = c2.con_poster

    WHERE con_status = 0 

    GROUP BY content.con_id
    ORDER BY con_posttime

This is untested. You might be able to join content twice and using COUNT(DISTINCT c2.con_id) AS pending and COUNT(DISTINCT c3.con_id) AS approved, if the CASE-statement is to slow, then you add c2.con_status = 0 in your join clause (for pending posts).

IFNULL is there just in case you wouldn't have any posts (which would never be true since the primary content-row would always exist. You can disallow current post in the JOIN-clause if you don't want to count it.)

For the last query, I would select all info for all users that you're processing (saving all IDs then call it using a user_id IN() instead, or a subselect (depending on how you make your selection).

Then sorting and processing the data in code instead. That minimizes the number of queries.

Answer 5

Here's another try at making it into one giant query. No idea how fast it will be though...

Edit: OK, third try. This should actually be pretty speedy if you set the right indexes.

SELECT 
    content.con_catid,
    content.con_posttime,
    content.con_url,
    content.con_id,
    content.con_title,
    categories.name, 
    users.username, 
    users.user_id, 
    stats1.website_url, 
    websites.website_id, 
    websites.website_shady,
    websites.website_in_ballast, 
    websites.website_out_ballast,
    (SELECT COALESCE(sum(dstats_hits),0)+website.website_in_ballast FROM domain_stats WHERE websites.website_id = domain_stats.dstats_domid) as total_in,
    (SELECT COALESCE(sum(dstats_hits_out),0)+website_out_ballast FROM domain_stats WHERE websites.website_id = domain_stats.dstats_domid) as total_out,
    (SELECT sum(dstats_hits_out_paid) FROM domain_stats WHERE websites.website_id = domain_stats.dstats_domid) as total_paid,
    (SELECT count(c2.con_id) FROM content c2 WHERE c2.con_poster = user.user_id AND con_status = 0) as pending,
    (SELECT count(c2.con_id) FROM content c2 WHERE c2.con_poster = user.user_id AND con_status = 2) as approved
FROM
    content
    LEFT JOIN categories ON (content.con_catid = categories.id) 
    LEFT JOIN users ON (content.con_poster = users.user_id)
    LEFT JOIN websites ON (website_userid=users.user_id)
WHERE
    con_status = 0
ORDER BY
    con_posttime