GROUP BY Not Using Index

[Conner Hewitt]

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!