Re: Subquery with range uses filesort

[David Marcus]

My Development computer has these settings:

key_buffer_size 47185920
innodb_buffer_pool_size 629145600

Query with subquery on InnoDB on Development computer
7.036 sec / 4.945 sec (first time query run after starting MySQL)
0.515 sec / 1.404 sec

Test PHP webpage with temporary table and MyISAM on Development computer
5.38 sec
5.49 sec

Test PHP webpage with subquery and MyISAM on Development computer
6.98 sec
7.21 sec

Test PHP webpage with temp table and InnoDB on Development computer
6.43 sec
5.76 sec

Test PHP webpage with subquery and InnoDB on Development computer
12.36 sec (first time query run after starting MySQL)
2.46 sec
2.45 sec
2.48 sec

The subquery version with

join History on
HistoryPlayer = PlayerID
and ( HistoryDate, HistoryDirector ) = ( select ...

is still very slow, even though it seems the logical way to do it:

Query with subquery join on HistoryDate-HistoryDirector on InnoDB on Development computer
29.344 sec / 51.091 sec (first time query run after starting MySQL)
14.383 sec / 47.830 sec

Query with subquery join on HistoryDate-HistoryDirector on MyISAM on Development computer
73.164 sec / 252.659 sec
74.381 sec / 253.096 sec

Test PHP webpage with subquery join on HistoryDate-HistoryDirector and InnoDB on Development computer
79.81 sec (first time query run after starting MySQL)
63.79 sec

The webserver has these settings:

key_buffer_size 268435456
innodb_buffer_pool_size 8388608

I'm not sure if the hosting company would want to change the InnoDB settings. Plus,
it is a shared server, so no guarantee my data would be cached.

Conclusions: InnoDB's caching is good. Both MyISAM and InnoDB are
doing a sort to find the most recent history record in the subquery
rather than using the index. For MyISAM, using the temporary table is
a bit faster than using the subquery. The subquery join on
HistoryDate-HistoryDirector is very slow.