MySQL Forums
Forum List  »  Optimizer & Parser

GROUP BY Not Using Index
Posted by: Conner Hewitt
Date: January 19, 2011 08:56AM

Hi Everyone,

I've been pulling my hair out trying to figure out an answer to this; any help is greatly appreciated.

I have a query that is not using an index which I believe is properly set up. Here is the query:

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

Here is the index that I believe it should be using:

KEY `pub_date_offer` (`publisher_id`,`datetime`,`offer_id`)

Now here is what is shown when EXPLAIN is ran with the SELECT query above:

id: 1
select_type: SIMPLE
table: leads
type: range
possible_keys: datetime,publisher_id,pub_date,pub_date_offer
key: pub_date
key_len: 12
ref: NULL
rows: 23184
Extra: Using where; Using temporary; Using filesort

And it completes in about 2.3311 seconds.

Now, if I change the GROUP BY in the query from 'offer_id' to 'datetime', it will use this index:

KEY `pub_date` (`publisher_id`,`datetime`)

and complete in 0.0016 seconds! a HUGE difference (especially when it's ran on a table that's identical to this one, but with around 6 million rows instead a few hundred thousand)! In the Extra field when I use EXPLAIN on the new query, it will only say 'Using where' and that's it. This makes me believe that the 'Using temporary; Using filesort' causes the query to be slow, which is caused by the query failing to use the correct index, however I have no idea why it won't use the 'pub_date_offer' index.

I'm sorry if I explained this poorly, please let me know if you need more info and I'll be happy to provide it!

Thanks!

Options: ReplyQuote


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


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.