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.

Answer 1

It looks like what's wrong is that help_mails.helper isn't indexed.

Answer 2

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.