Re: Subquery with range uses filesort

[Rick James]

> 1,SIMPLE,Player,ALL,PRIMARY,NULL,NULL,NULL,53985,19.23,"Using where"
> The three dots are really a list of 10384 player IDs.

Note that ~20% of the table is needed. Hence it is reasonable for the optimizer to punt on using the index, and simply do a "table scan".

> For each $PlayerID, it does
> | select HistoryEvent, HistoryDate, HistoryFinalMean,
> ...
> Here is a single query that does the same thing

Good, it should be done in a single step.

> where HistoryPlayer = PlayerID and HistoryDate <= '2012-06-30'
> order by HistoryDate desc, HistoryDirector desc limit 0,1 )
> 2,"DEPENDENT SUBQUERY",History,ref, PlayerDateDirector,PlayerDateDirector,4, ratingscentral.Player.PlayerID,8808,100.00, "Using where; Using filesort"
> PRIMARY KEY (`HistoryEvent`,`HistoryPlayer`),
> UNIQUE KEY `PlayerDateDirector` (`HistoryPlayer`, `HistoryDate`, `HistoryDirector`),

This involves a tricky optimization to make use of that KEY such that it reads only one row. The EXPLAIN implies that it is failing to do that that optimization. Suggest you file a bug at bugs.mysql.com, and focus on the optimization of this subquery. Point out that it could/should say "Using index" instead of "Using where; Using filesort". Also note that `HistoryEvent` is the only other thing in the SELECT, and that is part of the PRIMARY KEY, which is implicitly part of the secondary key mentioned.
Never mind -- don't file a bug report. You will get no sympathy since you are not using InnoDB. If particular, what I said about the PK (above) applies only to InnoDB. Switching to InnoDB may help performance right away!

In MyISAM, you could try adding
INDEX(`HistoryPlayer`, `HistoryDate`, `HistoryDirector`, `HistoryEvent`)
This might get it to "Using index" and maybe optimizing away from scanning ~8808 rows to find the latest one.

> Duration / Fetch
> 0.764 sec / 2.387 sec

What do you mean by "Duration" and "Fetch"?