1
\$\begingroup\$

I have a query consisting of UNIONs.

 SELECT CONCAT("You sent track request to ",u.username) AS ACTION, h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Request"
UNION
SELECT CONCAT("You Accepted track request from ",u.username) AS ACTION, h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Accept"
UNION
SELECT CONCAT("You Denied track request from ",u.username) AS ACTION, h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Deny"
UNION
SELECT CONCAT(u.username, " sent you Track Request") AS ACTION, h.`dateTime`  FROM login_user u
LEFT JOIN
 history h ON u.id = h.FromUserId WHERE h.`toUserId`=28 AND h.`Action`="Request"
  UNION
 SELECT CONCAT(u.username, " accepted your track request") AS ACTION, h.`dateTime`  FROM login_user u
 LEFT JOIN
 history h ON u.id = h.`fromUserId` WHERE h.`toUserId`=28 AND h.`Action`="Accept"
 UNION
 SELECT CONCAT(u.username, " Denied your track request") AS ACTION, h.`dateTime`  FROM login_user u
 LEFT JOIN
 history h ON u.id = h.`fromUserId` WHERE h.`toUserId`=28 AND h.`Action`="Deny"

Since all the constituent queries are almost same, I was wondering if I can use CASE or something to shorten and optimize the query?

\$\endgroup\$
  • \$\begingroup\$ They're three separate conditions, why would you want to combine them? \$\endgroup\$ – Quill Apr 14 '16 at 12:04
  • \$\begingroup\$ These three queries are to generate notification and i was wondering instead of doing union if there is other way around. \$\endgroup\$ – sum1 Apr 14 '16 at 12:07
  • \$\begingroup\$ Your Rev 2 basically stole @MarcusH's answer and rolled it into the question. Please never do that again; it makes a mess of the Code Review process. See What to do when someone answers for the rules. \$\endgroup\$ – 200_success Apr 14 '16 at 19:03
3
\$\begingroup\$

These constituent queries will never generate overlapping results, since their ACTION columns are all distinct. Therefore, you should be using UNION ALL instead of UNION to avoid the deduplication effort.

Also, I suspect you should be using INNER JOIN instead of LEFT JOIN because I doubt that you are interested in historical events that didn't occur.

For portability, string literals should be written using single quotes, like 'Request', rather than "Request". Allowing double quotes for string literals is a MySQLism — standard SQL uses double quotes for identifiers, the way MySQL uses backticks.

Take care to be consistent in your indentation and capitalization. You wrote h.FromUserId instead of h.`fromUserId` in one case. You also call the result column ACTION, which is capitalized differently from history.Action. The result also had "Accepted", "Denied", and "Track Request" oddly capitalized in various places.

Anyway, to address your specific concern, I think that this formulation using CASE could work for you.

SELECT Action, dateTime
    FROM (
        SELECT CASE
               WHEN h.Action = 'Request' THEN CONCAT('You sent track request to ', u.username)
               WHEN h.Action = 'Accept'  THEN CONCAT('You accepted track request from ', u.username)
               WHEN h.Action = 'Deny'    THEN CONCAT('You denied track request  from ', u.username)
               END CASE AS Action
             , h.dateTime
             , h.fromUserId AS self
            FROM history h
                INNER JOIN login_user u
                    ON h.toUserId = u.id
            WHERE h.Action IN ('Request', 'Accept', 'Deny')    -- possibly superfluous
        UNION ALL
        SELECT CASE
               WHEN h.Action = 'Request' THEN CONCAT(u.username, ' sent you track request')
               WHEN h.Action = 'Accept'  THEN CONCAT(u.username, ' accepted your track request')
               WHEN h.Action = 'Deny'    THEN CONCAT(u.username, ' denied your track request')
               END CASE AS Action
             , h.dateTime
             , h.toUserId AS self
            FROM history h
                INNER JOIN login_user u
                    ON h.fromUserId = u.id
            WHERE h.Action IN ('Request', 'Accept', 'Deny')    -- possibly superfluous
    ) AS msg
    WHERE self = 28
    ORDER BY dateTime;
\$\endgroup\$
  • \$\begingroup\$ The result of this query doesn't match with the result of original query \$\endgroup\$ – sum1 Apr 15 '16 at 4:02
  • \$\begingroup\$ I had misinterpreted the sense of the join. Rev 2 should be correct, I think. \$\endgroup\$ – 200_success Apr 15 '16 at 5:24
  • \$\begingroup\$ Yeah this one does :D \$\endgroup\$ – sum1 Apr 15 '16 at 6:01
1
\$\begingroup\$

You can add union between your queries:

SELECT CONCAT("You sent track request to ",u.username) AS ACTION,  
h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Request";
UNION
 SELECT CONCAT("You Accepted track request from ",u.username) AS ACTION, h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Accept";
UNION
SELECT CONCAT("You Denied track request from ",u.username) AS ACTION,   h.`dateTime`  FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND h.`Action`="Deny";

read more here http://dev.mysql.com/doc/refman/5.7/en/union.html

else you can add case when

SELECT 
case 
   when h.`Action`="Request" then CONCAT("You sent track request to ",u.username) 
   when h.`Action`="Accept" then CONCAT("You Accepted track request from ",u.username)
  when h.`Action`="Deny" then CONCAT("You Denied track request from ",u.username)
  else ''
end AS ACTION,  
h.`dateTime`  
FROM login_user u
LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 
\$\endgroup\$
0
\$\begingroup\$

Prefer inner joins to outer joins

 SELECT CONCAT("You sent track request to ",u.username) AS ACTION, h.`dateTime`  FROM login_user u
 LEFT JOIN
history h ON u.id = h.`toUserId` WHERE h.`fromUserId`=28 AND    h.`Action`="Request";

This doesn't need to be a LEFT JOIN. With a LEFT JOIN, history.fromUserId can be NULL, but you explicitly foreclose that by saying that it must equal 28.

 SELECT CONCAT('You sent track request to ', u.username) AS ACTION, h.`dateTime`
 FROM login_user u INNER JOIN history h ON u.id = h.`toUserId`
 WHERE h.`fromUserId`=28 AND h.`Action` = 'Request';

This uses the more efficient INNER JOIN to return the same data set.

An inner join looks for all entries that match in the two tables. The left outer join looks for all entries that match plus all entries in the first table that have no match in the second table. But your WHERE clause removes all the entries with no match in the second table. So you can do the simpler, less expensive inner join rather than the more expensive outer join.

IF and CASE

The commands needed to return different output based on column values are IF and CASE. If there are only three Action values and you are testing for all of them, then CASE may be easier to write and even more performant.

If there are more than three Action values such that you aren't testing for all of them, the UNION may be more performant. Note that in the CASE version unifying them, you have to use an IN clause. When you use an IN, it runs the query once for each value in the IN. As such, it is very similar to taking the UNION of the results of the three separate queries.

Consider doing this in code

Rather than converting from an action to a phrase in SQL, consider doing so in code calling the SQL. This would be trivial to do in any coding language with just two queries:

SELECT u.username, h.dateTime, h.Action 
FROM login_user u INNER JOIN history h ON u.id = h.toUserId
WHERE h.fromUserId = 28

SELECT u.username, h.dateTime, h.Action 
FROM login_user u INNER JOIN history h ON u.id = h.fromUserId
WHERE h.toUserId = 28

Then the calling code could convert the Action and username into a phrase.

Obviously this won't work if you are generating a report direct from the database, but if you do any post-processing you will likely find the code version more flexible.

Consider making Action into an integer

String comparisons are less efficient than numeric comparisons and take up more room if you index the columns. If you are restricting queries with it, you'd generally be better off creating an actions domain table. Then history could just store an integer ID. It would also be slightly more efficient to process in code later.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.