Thank you for the in depth answer!
You're right, I reread the GROUP BY optimization page
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html and tested it, for it to use the index on offer_id in GROUP BY, I would have to have an index set up as:
INDEX('offer_id','publisher_id','datetime')
And within the WHERE clause have it start out with 'offer_id IS NOT NULL' or something similar. This however then causes it to seek through every single row because offer_id isn't null for all rows. The best index as you said is (publisher_id, datetime).
I have another question though, two actually:
1. Say I end up having a table with 100 million rows, and I need to perform an aggregate function on it such as COUNT(*). From my experience, if MySQL has to scan all 100 million rows, this will take a very long time (minutes), even with an index being used. Is there any way to make data retrieval virtually instant, even with this many rows being used, or is it doomed to take minutes worth of time to load?
2. Rick James, do you do any freelance work for database/query optimization?
Thanks!
Conner