I should emphasize that "filesort" is not the end of the world. It is merely a symptom of a complex query, and it may not be avoidable.
Meanwhile, let's see what we can do for you...
In this
select *
from Player p
join History h on h.HistoryPlayer = p.PlayerID
JOIN
( SELECT HistoryDate
from History
where HistoryPlayer = PlayerID
and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc
limit 0,1 ) x ON x.HistoryDate = h.HistoryDate
"HistoryDirector desc" seems to be unnecessary.
That would let you do MAX(HistoryDate) instead of ORDER BY and LIMIT.
Note that if there are two History rows for a given HistoryDate and PlayerID, the final result will have multiple rows. Is this what you expect?
Hmmm... Now I am confused by "where HistoryPlayer = PlayerID" in the subquery.
What is the query trying to do? Perhaps "find the latest info (from last year) for each player"? If so, then it is a "groupwise max" problem. Search for that.
Could we see the
SHOW CREATE TABLE History;
SHOW CREATE TABLE Players;
It smells like you are missing some indexes.
Notice how this one:
1,PRIMARY,Player,ALL,NULL,NULL,NULL,NULL,53985,100.00,
1,PRIMARY,History,ALL,NULL,NULL,NULL,NULL,880850,100.00,"Using where; Using join buffer"
2,"DEPENDENT SUBQUERY",History,ref,PlayerDateDirector,PlayerDateDirector,4,ratingscentral.Player.PlayerID,8808,100.00,"Using where; Using index; Using filesort"
fails to use any indexes. In fact it does a cross-join between Player and History before touching the _dependent_ subquery. That should take a looong time.
You have an awful number of indexes on Player. Most of them are probably not worth having.
Sorry, I don't (yet) have the answers you want.