Combine Three Sql Queries

I have the following three SQL queries. I would like to combine them into one is possible, either using the current method, or another method if it would be more efficient. NOTE: I

Solution 1:

This might work for you.

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgIdAND (imgWidth != $maxImageWidthAND imgHeight = $maxImageHeightOR imgWidth = $maxImageWidthAND imgHeight != $maxImageHeightOR imgWidth = $maxImageWidthAND imgHeight = $maxImageHeight);

I also believe this is equivalent, although I'm not sure.

SElECTCOUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNIONALLSELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId 
AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight);

As per the request, here is a single query that returns 3 rows.

SELECT'ALL EQUAL'AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNIONALLSELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight
UNIONALLSELECT'WIDTH EQUAL'AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNIONALLSELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight;
UNIONALLSELECT'HEIGHT EQUAL'AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images  
    UNIONALLSELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight;

Solution 2:

I believe that a CASE and a GROUP BY are needed:

SELECTCASEWHEN imgWidth  = $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN0WHEN imgWidth != $maxImageWidth AND imgHeight  = $maxImageHeight
       THEN1WHEN imgWidth  = $maxImageWidth AND imgHeight != $maxImageHeight
       THEN2ENDAS count_type,
       COUNT(*) AS imgCount
  FROM (SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
        UNION ALL 
        SELECT imgHeight, imgWidth, primaryId FROM secondary_images
       ) AS union_table
 WHERE primaryId = $imgId
   AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)
 GROUPBY count_type;

It is at least tempting to push the WHERE clause down into the UNION subqueries, but the optimizer should do that anyway. Do check that it does do that by studying the EXPLAIN output. If the optimizer does not do the predicate pushdown automatically, you'd write:

SELECT CASE
       WHEN imgWidth  = $maxImageWidthAND imgHeight  = $maxImageHeight
       THEN 0
       WHEN imgWidth != $maxImageWidthAND imgHeight  = $maxImageHeight
       THEN 1
       WHEN imgWidth  = $maxImageWidthAND imgHeight != $maxImageHeight
       THEN 2
       END AS count_type,
       COUNT(*) AS imgCount
  FROM (SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
         WHERE primaryId = $imgId  -- or: imgId = $imgIdAND (imgWidth = $maxImageWidthOR imgHeight = $maxImageHeight)
        UNION ALL 
        SELECT imgHeight, imgWidth, primaryId FROM secondary_images
         WHERE primaryId = $imgIdAND (imgWidth = $maxImageWidthOR imgHeight = $maxImageHeight)
       ) AS union_table
 GROUP BY count_type;

The disadvantage of this solution as written is that the $maxImageWidth and $maxImageHeight variables are used 4 times each (and $imgId once). If you are preparing the statement as shown (embedding the values in the string representing the SQL), it isn't too bad. If you are using placeholders, you need a naming scheme for the placeholders if possible (:img_ht, :img_wd, :img_id (or at least :1, :2, :3) in place of the ? placeholders) so you only pass each value once in the EXECUTE statement. That capability depends on your DBMS, though; MySQL is one of a number of DBMS that do not support this facility.

Solution 3:

Do you have indexes on primaryId in your primary_images and secondary_images tables? If yes, then you should push the where clause inside so that the indexes can be used.

SElECT COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images WHERE imgId = $imgIdAND imgWidth != $maxImageWidthAND imgHeight = $maxImageHeight 
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images WHERE primaryId = $imgIdAND imgWidth != $maxImageWidthAND imgHeight = $maxImageHeight
) AS union_table

Otherwise it will be a full table scan to find the imgId as union_table is a temporary table created by Mysql and doesn't have any indexes.