MySQL query with join optimization

Question

I got a query:

SELECT a.nick,grp,count(*) FROM help_mails h JOIN accounts a ON h.helper=a.id WHERE closed=1 GROUP BY helper, grp, a.nick

What is wrong with this join? When I made 2 queries:

SELECT helper,grp,count(*) FROM help_mails h WHERE closed=1 GROUP BY helper, grp; SELECT nick FROM accounts WHERE id IN (...) It is 100 times faster.

EXPLAIN returns this:

id     select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  h   ref     closed  closed  1   const   1846    Using temporary; Using filesort
1   SIMPLE  a   ref     PRIMARY     PRIMARY     4   margonem.h.helper   1   Using where; Using index

accounts.id, help_mails.grp and help_mails.closed got indexes.

Quassnoi Quassnoi · Accepted Answer · 2009-06-04T12:53:56

Note that your first query is not same as the second ones.

If you have same NICK for two account's, COUNT(*)'s for these accounts will be merged together in the first query and returned separately in the second one.

If you want separate COUNT's for separate account's to be always returned, you may combine your queries into one:

SELECT  a.nick, gpr, cnt
FROM    (
        SELECT  helper, grp, COUNT(*) AS cnt
        FROM    help_mails h
        WHERE   closed = 1
        GROUP BY
                helper, grp
        ) ho
JOIN    accounts a
ON      a.id = ho.helper

or change a GROUP BY condition for the first query:

SELECT  a.nick, grp, count(*)
FROM    help_mails h
JOIN    accounts a
ON      h.helper = a.id
WHERE   closed = 1
GROUP BY
        helper, grp, a.id, a.nick

Building a composite index on help_mails (closed, helper, grp) will help you a lot, since it will be used in GROUP BY.

MySQL query with join optimization

2 Answers