CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1959

Better query for union join

table members

id primary key
username varchar

table mpoints

id primary key
money int
member_id foreign key

table gpoints

id primary key
price int
member_id foreign key

mpoints and gpoints are almost the same thing related to the members table, I need to find out the data which sum of money and price > 200 and counts > 2, here is my solution

SELECT
    `t`.`username`, `t`.`p`
FROM
(
    SELECT
        `members`.`username`,
        `price` AS `p`
    FROM
        `members`
        LEFT JOIN `gpoints` ON `members`.`id` = `gpoints`.`member_id`
    UNION ALL
    SELECT
        `members`.`username`,
        `money` AS `p`
    FROM
        `members`
        LEFT JOIN `mpoints` ON `members`.`id` = `mpoints`.`member_id`
) AS `t`
GROUP BY `t`.`username`
HAVING COUNT(`t`.`p`) >= 2 AND `t`.`p` > 200

But member is over five million and both points table are over fifty thousands, so the query is quite slow, can I improve the performance by only adjust the query?

Upvotes: 0

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Your correct query would be:

SELECT t.username, SUM(t.p) as p
FROM (SELECT m.username, price AS p
      FROM members m LEFT JOIN 
           gpoints g
           ON m.id = g.member_id
      UNION ALL
      SELECT m.username, `money` AS p
      FROM members m LEFT JOIN 
           mpoints p
           ON m.id = p.member_id
     ) t
GROUP BY t.username
HAVING COUNT(t.p) >= 2 AND SUM(t.p) > 200;

Note the use of SUM() in the SELECT and the HAVING.

For better performance, you want indexes on gpoints(member_id, price) and mpoints(member_id, money).

Upvotes: 0

Ron Smith
Ron Smith

Reputation: 3266

I would only union the gpoints and mpoints tables, group by member_id from that union, and join that dataset to the members table:

select
    m.username,
    t.p
from members as m
    inner join (
               select
                   p.member_id,
                   sum(p) as p
               from (
                    select
                        member_id,
                        price as p
                    from gpoints
                    union all
                    select
                        member_id,
                        money as p
                    from mpoints
                    ) as p
               group by
                   member_id
               having
                   count(p.p) >=2
                   and sum(p.p) > 200     
               ) t
           on t.member_id = m.id

Alternatively, you could left join a grouping for each table and add those sums:

select
    m.username,
    gp.p + mp.p as p
from members as m
    left join (
               select
                   member_id,
                   sum(price) as p,
                   count(price) as cnt
               from gpoints
               group by member_id
               ) gp
        on gp.member_id = m.id
    left join (
               select
                   member_id,
                   sum(money) as p,
                   count(money) as cnt
               from mpoints
               group by member_id
               ) mp
        on mp.member_id = m.id
where
    ifnull(gp.p,0) + ifnull(mp.p,0) > 200
    and ifnull(gp.cnt,0) + ifnull(mp.cnt,0) >= 2

Upvotes: 2

Related Questions