Re: Subquery with range uses filesort

[David Marcus]

> Define "shared".
> * One instance of mysqld; each user has one database in it.
> * Separate instances of mysqld for each user.

I believe there is one instance of mysqld, but I'm not sure how to
confirm that. Each user can have one or more databases.

> I can see their reluctance to change anything.

I don't know how reluctant they would be; I haven't asked them. They
might have another server tuned for InnoDB.

> > doing a sort to find the most recent history record in the
> > subquery rather than using the index.
>
> Well... Data blocks and Index blocks are each cached. The difference
> in speed (between first and subsequent runs) is almost entirely due
> to caching. That is, if it used an index one time, it will use the
> index the other time. The first run is mostly I/O bound; subsequent
> runs are mostly CPU bound.

My statement about "doing a sort" was based on the output of the
"explain" statements.

> Please also try
>
> join History h on HistoryPlayer = PlayerID
> JOIN ( select ... ) x
> ON h.History = x.HistoryDate AND h.HistoryDirector = x.HistoryDirector

select
PlayerID, PlayerName, PlayerPrimaryClub, PlayerCountry,
H.HistoryEvent, H.HistoryDate, H.HistoryFinalMean, H.HistoryFinalStDev
from Player
join History as H on HistoryPlayer = PlayerID
join
( select HistoryDate, HistoryDirector
from History
where HistoryPlayer = PlayerID and HistoryDate <= '2012-06-30'
order by HistoryDate desc, HistoryDirector desc limit 0,1 ) as S
on H.HistoryDate = S.HistoryDate and H.HistoryDirector = S.HistoryDirector
where PlayerID in (...)

Error Code: 1054. Unknown column 'PlayerID' in 'where clause'