MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY Not Using Index
Posted by: Conner Hewitt
Date: January 21, 2011 05:04PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
8350
January 19, 2011 08:56AM
3962
January 20, 2011 10:02PM
Re: GROUP BY Not Using Index
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.