MySQL Forums
Forum List  »  Optimizer & Parser

Re: group by date(indexed datetime) hard to optimize
Posted by: Rick James
Date: March 31, 2010 10:58PM

1. "date(date)" -- taking any function of a column disables using any index in connection with optimizing. Since `date` is a DATETIME, I see the necessity of doing that function call.

2. GROUP BY implicitly does an ORDER BY unless you tell it not to via ORDER BY NULL. There are two ways GROUP BY can be done -- one is to sort the data (or discover that it is already sorted), then go through it in a simple pass. The other is to build a hash table; this leads to creating the result in a seemingly random order. Presumably you have the latter case.

3. Try this. Add an index, and change the inner GROUP BY:
INDEX(player_age, player_gender, date)
group by player_age, player_gender, date(date)
I don't know if it will help, but it might.

4. This is essentially impossible to optimize; it requires ORDER(N*N) operations:
ON a.fromAge<=thing.player_age and thing.player_age<=a.toAge
That's probably the main sluggishness.

5. How long does the whole SELECT take? How long does the subquery, by itself, take?

Options: ReplyQuote

Written By
Re: group by date(indexed datetime) hard to optimize
March 31, 2010 10:58PM

Sorry, you can't reply to this topic. It has been closed.

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.