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 · Accepted Answer · 2009-06-04 12:59:16Z

5

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.

edited Jun 4, 2009 at 12:59

answered Jun 4, 2009 at 12:53

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thinker Over a year ago

Thanks :) The first query with inner select works fast as it should :)

Quassnoi Over a year ago

Thinker: it will work even faster if you create a composite index :)

chaos · Accepted Answer · 2009-06-04 12:51:35Z

1

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

answered Jun 4, 2009 at 12:51

chaos

125k34 gold badges306 silver badges310 bronze badges

2 Comments

Thinker Over a year ago

Adding index to helper field doesn't improve anything, I checked it.

chaos Over a year ago

That doesn't make any sense to me, but okay. Try a compound key on closed and helper.

Collectives™ on Stack Overflow

MySQL query with join optimization

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related