Subquery with range uses filesort
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.