Re: group by date(indexed datetime) hard to optimize

[Rick James]

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?