CODEWITHSUNDEEP
mysql
Tom
Tom

Reputation: 4577

Convert MySQL query from UNION to something more efficient

I have a custom messaging feature on my website and when I display the number of messages a user has, I do so using a union query since there are two types of messages.

I do so as follows:

SELECT COUNT(m.messageid) type_a_messagecount
FROM messages m,
messagerecipients r
WHERE m.messageid = r.messageid AND
(
m.type = 0 OR
m.type = 2
) AND
r.read = 0 AND
r.trashed = 0 AND
r.deleted = 0 AND
r.userid = 1 AND
r.authorid <> 1
UNION
SELECT COUNT(m.messageid) type_b_messagecount
FROM messages m,
messagerecipients r
WHERE m.messageid = r.messageid AND
(
m.type = 1
) AND
r.read = 0 AND
r.trashed = 0 AND
r.deleted = 0 AND
r.userid = 1 AND
r.authorid <> 1

This works but something tells me it's horribly inefficient.

Is there a way to streamline this or am I stuck using a UNION query?

Thanks.

Upvotes: 0

Views: 485

Answers (3)

Thomas
Thomas

Reputation: 64645

Select Sum(Case When M.type In(0,2) Then 1 Else 0 End) As type_a_messagecount
    , Sum(Case When M.type = 1 Then 1 Else 0 End) As type_b_messagecount
From messages As M
    Join messagerecipients As R
        On R.messageid = M.messageid
Where R.read = 0 
    And R.trashed = 0 
    And R.deleted = 0 
    And R.userid = 1 
    And R.authorid <> 1

Upvotes: 1

nate c
nate c

Reputation: 9005

Subquery with a case statement will give you what you want:

SELECT 
    count(m.messageid), type_
FROM
    (
        SELECT 
            m.messageid
            CASE 
                WHEN m.type = 0 THEN 'a'
                WHEN m.type = 1 THEN 'b'
                WHEN m.type = 2 THEN 'a'
            END as type_
            FROM 
                messages m,messagerecipients r
            WHERE 
                m.messageid = r.messageid 
                AND r.read = 0
                AND r.trashed = 0
                AND r.deleted = 0
                AND r.userid = 1 AND
                AND r.authorid <> 1
    ) as t
GROUP BY type_

Upvotes: 1

Damp
Damp

Reputation: 3348

What about

SELECT COUNT(m.messageid),m.type
FROM messages m,
  messagerecipients r
WHERE m.messageid = r.messageid AND
  m.type IN (0,1,2) AND
  r.read = 0 AND
  r.trashed = 0 AND
  r.deleted = 0 AND
  r.userid = 1 AND
  r.authorid <> 1
GROUP BY m.type

And then programmatically adding the results for type 0 and 2

Upvotes: 1

Related Questions