MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY Not Using Index
Posted by: Rick James
Date: January 20, 2011 10:02PM

SELECT  id, offer_id, country, sub_id, COUNT(*) total_clicks
    FROM  leads
    WHERE  publisher_id = '18'
      AND  datetime BETWEEN '2010-01-11 00:00:00'  AND  '2011-01-18 23:59:59'
    GROUP BY  offer_id

The optimizer did what it could in the WHERE clause, and could not get to the GROUP BY. Here's how I would predict the best INDEX for your query:
1. Do all "="s -- just `publisher_id`
2. Do one more thing, with preference to WHERE -- `datetime`
3. That's all.
So, the best index is probably (publisher_id, datetime)

Don't cry over "GROUP BY Not Using Index"; sometimes it simply can't be done.

It would really help if you had provided SHOW CREATE leads \G
I'll guess that the 12 is the combined length of publisher_id and datetime. This implies that it probably did not get to the third field in the index you gave.

INDEX(`publisher_id`,`datetime`,`offer_id`)
INDEX(`publisher_id`,`datetime`)
The former covers for the latter; there is no need to have both. (DROP the latter.)

6M rows and doing aggregate functions? Sounds like you need a "summary table".


Now, on to another issue...
Is there only one id per offer_id? Only one country per offer_id, only one sub_id? If not, which one do you expect this query to give you?

Normally, in a GROUP BY, you state all the fields that are not "aggregates" (COUNT(*), in your case). That is, I would expect
GROUP BY id, offer_id, country, sub_id
(in some order)

Options: ReplyQuote


Subject
Views
Written By
Posted
8351
January 19, 2011 08:56AM
Re: GROUP BY Not Using Index
3963
January 20, 2011 10:02PM
2948
January 21, 2011 05:04PM
2556
January 21, 2011 11:07PM
2590
February 08, 2011 11:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.