MySQL Forums
Forum List  »  Optimizer & Parser

Subquery with range uses filesort
Posted by: David Marcus
Date: November 16, 2013 02:42PM

In query #1 below, MySQL uses an index to find the right record. But,
when I try to use essentially the same thing in a subquery (queries
#2, #3, #4), MySQL does a filesort. Is there any way to convince it to
not do a filesort?

Also, I don't understand why it reads "ALL" of the History table in
queries #2 and #3.

This is with MySQL Community Server 5.5.33.

1. The simple query and the explain extended output:

explain extended select HistoryPlayer, HistoryDate, HistoryDirector
from History
where HistoryPlayer = 5000 and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc limit 0,1

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,History,range,PlayerDateDirector,PlayerDateDirector,7,NULL,2,100.00,"Using where; Using index"

2. The query with the subquery:

explain extended select * from Player
join History on
( HistoryPlayer, HistoryDate, HistoryDirector ) =
( select HistoryPlayer, HistoryDate, HistoryDirector
from History
where HistoryPlayer = PlayerID and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc limit 0,1 )

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
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"

3. #2 with the join condition on HistoryPlayer changed:

explain extended select * from Player
join History on HistoryPlayer = PlayerID and
( HistoryDate, HistoryDirector ) =
( select HistoryDate, HistoryDirector
from History
where HistoryPlayer = PlayerID and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc limit 0,1 )

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,PRIMARY,History,ALL,PlayerDateDirector,NULL,NULL,NULL,880850,100.00,
1,PRIMARY,Player,eq_ref,PRIMARY,PRIMARY,4,ratingscentral.History.HistoryPlayer,1,100.00,"Using where"
2,"DEPENDENT SUBQUERY",History,ref,PlayerDateDirector,PlayerDateDirector,4,ratingscentral.Player.PlayerID,8808,100.00,"Using where; Using index; Using filesort"

4. #3 with a straight_join:

explain extended select * from Player
straight_join History on HistoryPlayer = PlayerID and
( HistoryDate, HistoryDirector ) =
( select HistoryDate, HistoryDirector
from History
where HistoryPlayer = PlayerID and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc limit 0,1 )

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,PRIMARY,Player,ALL,PRIMARY,NULL,NULL,NULL,53985,100.00,
1,PRIMARY,History,ref,PlayerDateDirector,PlayerDateDirector,4,ratingscentral.Player.PlayerID,8808,100.00,"Using where"
2,"DEPENDENT SUBQUERY",History,ref,PlayerDateDirector,PlayerDateDirector,4,ratingscentral.Player.PlayerID,8808,100.00,"Using where; Using index; Using filesort"

Here are the tables:

create table History (
HistoryEvent integer unsigned not null,
HistoryPlayer integer unsigned not null, # PlayerID
HistoryDate date not null,
HistoryDirector integer unsigned not null,
HistoryReportID integer not null,
HistoryInitialMean smallint unsigned not null,
HistoryInitialStDev smallint unsigned not null,
HistoryFinalMean smallint unsigned not null,
HistoryFinalStDev smallint unsigned not null,
primary key ( HistoryEvent, HistoryPlayer ),
unique index EventReportID ( HistoryEvent, HistoryReportID ),
index EventInitialMean ( HistoryEvent, HistoryInitialMean ),
index EventFinalMean ( HistoryEvent, HistoryFinalMean ),
unique index PlayerDateDirector ( HistoryPlayer, HistoryDate, HistoryDirector ) )
default character set latin1 collate latin1_german1_ci pack_keys=1 engine=MyISAM;

create table Player (
PlayerID integer unsigned not null,
PlayerName varchar(40) not null,
PlayerPrimaryClub integer unsigned not null,
PlayerAddress1 varchar(50) not null,
PlayerAddress2 varchar(50) not null,
PlayerCity varchar(30) not null,
PlayerState char(2) not null,
PlayerProvince varchar(25) not null,
PlayerPostalCode varchar(16) not null,
PlayerCountry char(3) not null,
PlayerEmail varchar(254) not null,
PlayerBirth date not null,
PlayerDeceased enum('No','Yes') not null,
PlayerGender char(1) not null,
PlayerUSATT_ID mediumint unsigned not null,
PlayerTTA_ID mediumint unsigned not null,
PlayerSport tinyint unsigned not null,
PlayerMean smallint unsigned not null,
PlayerStDev smallint unsigned not null,
PlayerLastEvent integer unsigned not null,
PlayerLastPlayed date not null,
PlayerNotify enum('No','Yes') not null,
PlayerPassword tinyblob not null,
PlayerRevision smallint unsigned not null,
PlayerLastModified timestamp not null default current_timestamp on update current_timestamp,
primary key ( PlayerID ),
index Rank ( PlayerSport, PlayerGender, PlayerDeceased, PlayerMean ),
index List ( PlayerSport, PlayerDeceased, PlayerName ),
index Name ( PlayerName ),
index USATT_ID ( PlayerUSATT_ID ),
index TTA_ID ( PlayerTTA_ID ),
index Mean ( PlayerMean ),
index StDev ( PlayerStDev ),
index Birth ( PlayerBirth ),
index PostalCode ( PlayerPostalCode ),
index ClubMean ( PlayerPrimaryClub, PlayerMean ),
index GenderMean ( PlayerGender, PlayerMean ),
index StateMean ( PlayerState, PlayerMean ),
index ProvinceMean ( PlayerProvince, PlayerMean ),
index CountryMean ( PlayerCountry, PlayerMean ),
index SportMean ( PlayerSport, PlayerMean ),
index LastPlayedMean ( PlayerLastPlayed, PlayerMean ),
index ClubName ( PlayerPrimaryClub, PlayerName ),
index GenderName ( PlayerGender, PlayerName ),
index StateName ( PlayerState, PlayerName ),
index ProvinceName ( PlayerProvince, PlayerName ),
index CountryName ( PlayerCountry, PlayerName ),
index SportName ( PlayerSport, PlayerName ),
index LastPlayedName ( PlayerLastPlayed, PlayerName ) )
default character set latin1 collate latin1_german1_ci pack_keys=1 engine=MyISAM;



Edited 1 time(s). Last edit at 11/16/2013 02:51PM by David Marcus.

Options: ReplyQuote


Subject
Views
Written By
Posted
Subquery with range uses filesort
2870
November 16, 2013 02:42PM
1133
November 17, 2013 07:42PM
1074
November 17, 2013 08:01PM
1078
November 18, 2013 04:33PM
1550
November 18, 2013 07:54PM
1168
November 19, 2013 04:10PM
1165
November 19, 2013 08:36PM
1233
November 21, 2013 02:55PM
1430
December 05, 2013 03:46PM
1061
December 07, 2013 02:10PM
1075
December 09, 2013 06:08PM
1091
December 14, 2013 09:51PM
1045
December 14, 2013 10:17PM
1136
December 15, 2013 11:29PM
1141
December 17, 2013 06:58PM
1065
December 18, 2013 05:49PM
1092
December 18, 2013 08:28PM
1161
December 19, 2013 10:13PM
1097
December 19, 2013 10:28PM
1007
December 20, 2013 07:06PM
1094
December 21, 2013 07:36AM
1123
December 22, 2013 10:01AM
1174
December 22, 2013 10:45AM
1035
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.