Re: Subquery with range uses filesort
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.
Subject
Views
Written By
Posted
4099
November 16, 2013 02:42PM
1449
November 17, 2013 07:42PM
1464
November 17, 2013 08:01PM
1464
November 18, 2013 04:33PM
2001
November 18, 2013 07:54PM
1687
November 19, 2013 04:10PM
1565
November 19, 2013 08:36PM
1627
November 21, 2013 02:55PM
1833
December 05, 2013 03:46PM
1478
December 07, 2013 02:10PM
1448
December 09, 2013 06:08PM
1550
December 14, 2013 09:51PM
1413
December 14, 2013 10:17PM
1487
December 15, 2013 11:29PM
Re: Subquery with range uses filesort
1539
December 17, 2013 06:58PM
1498
December 18, 2013 05:49PM
1531
December 18, 2013 08:28PM
1562
December 19, 2013 10:13PM
1531
December 19, 2013 10:28PM
1394
December 20, 2013 07:06PM
1480
December 21, 2013 07:36AM
1518
December 22, 2013 10:01AM
1580
December 22, 2013 10:45AM
1463
December 25, 2013 09:30PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.