MySQL Forums :: Optimizer & Parser :: Subquery with range uses filesort


Advanced Search

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 2703 David Marcus 11/16/2013 02:42PM
Re: Subquery with range uses filesort 1104 Rick James 11/17/2013 07:42PM
Re: Subquery with range uses filesort 1037 David Marcus 11/17/2013 08:01PM
Re: Subquery with range uses filesort 1048 Rick James 11/18/2013 04:33PM
Re: Subquery with range uses filesort 1496 David Marcus 11/18/2013 07:54PM
Re: Subquery with range uses filesort 1097 Rick James 11/19/2013 04:10PM
Re: Subquery with range uses filesort 1137 David Marcus 11/19/2013 08:36PM
Re: Subquery with range uses filesort 1206 Rick James 11/21/2013 02:55PM
Re: Subquery with range uses filesort 1336 David Marcus 12/05/2013 03:46PM
Re: Subquery with range uses filesort 1022 Rick James 12/07/2013 02:10PM
Re: Subquery with range uses filesort 1048 David Marcus 12/09/2013 06:08PM
Re: Subquery with range uses filesort 1059 David Marcus 12/14/2013 09:51PM
Re: Subquery with range uses filesort 1023 David Marcus 12/14/2013 10:17PM
Re: Subquery with range uses filesort 1096 Rick James 12/15/2013 11:29PM
Re: Subquery with range uses filesort 1115 David Marcus 12/17/2013 06:58PM
Re: Subquery with range uses filesort 1030 Rick James 12/18/2013 05:49PM
Re: Subquery with range uses filesort 1058 David Marcus 12/18/2013 08:28PM
Re: Subquery with range uses filesort 1124 Rick James 12/19/2013 10:13PM
Re: Subquery with range uses filesort 1065 David Marcus 12/19/2013 10:28PM
Re: Subquery with range uses filesort 980 Rick James 12/20/2013 07:06PM
Re: Subquery with range uses filesort 1058 David Marcus 12/21/2013 07:36AM
Re: Subquery with range uses filesort 1090 Rick James 12/22/2013 10:01AM
Re: Subquery with range uses filesort 1143 David Marcus 12/22/2013 10:45AM
Re: Subquery with range uses filesort 1012 David Marcus 12/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.