current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks :) The first query with inner select works fast as it should :) –  Thinker Jun 4 '09 at 13:01
    
Thinker: it will work even faster if you create a composite index :) –  Quassnoi Jun 4 '09 at 13:02
up vote 1 down vote

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

share|improve this answer
    
Adding index to helper field doesn't improve anything, I checked it. –  Thinker Jun 4 '09 at 12:56
    
That doesn't make any sense to me, but okay. Try a compound key on closed and helper. –  chaos Jun 4 '09 at 12:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.