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)