Rick,
Thank you.
I can avoid the filesort by creating a temporary table of player IDs,
then doing this query for each ID and using the result to update a row
of the temporary table:
select HistoryPlayer, HistoryDate, HistoryDirector
from History
where HistoryPlayer = PlayerID and HistoryDate <= '2013-01-01'
order by HistoryDate desc, HistoryDirector desc limit 0,1
That's essentially what my website does now.
> What is the query trying to do? Perhaps "find the latest info (from
> last year) for each player"?
Yes. Players play tournaments. The query is finding the last
tournament the player played on or before a certain date. The reason for the
HistoryDirector is that if there are two tournaments on the same date,
I want the one submitted by the director with the larger director ID.
> You have an awful number of indexes on Player. Most of them are
> probably not worth having.
The actual queries may have conditions on various columns and sorted
in various ways. The queries are run by this webpage:
http://www.ratingscentral.com/PlayerSearch.php
The date cutoff and subquery or temporary table comes into play if you
enter something into the "As of date" field.
'CREATE TABLE `History` (
`HistoryEvent` int(10) unsigned NOT NULL DEFAULT ''0'',
`HistoryPlayer` int(10) unsigned NOT NULL DEFAULT ''0'',
`HistoryDate` date NOT NULL DEFAULT ''0000-00-00'',
`HistoryDirector` int(10) unsigned NOT NULL,
`HistoryReportID` int(11) NOT NULL DEFAULT ''0'',
`HistoryInitialMean` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`HistoryInitialStDev` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`HistoryFinalMean` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`HistoryFinalStDev` smallint(5) unsigned NOT NULL DEFAULT ''0'',
PRIMARY KEY (`HistoryEvent`,`HistoryPlayer`),
UNIQUE KEY `EventReportID` (`HistoryEvent`,`HistoryReportID`),
UNIQUE KEY `PlayerDateDirector` (`HistoryPlayer`,`HistoryDate`,`HistoryDirector`),
KEY `EventInitialMean` (`HistoryEvent`,`HistoryInitialMean`),
KEY `EventFinalMean` (`HistoryEvent`,`HistoryFinalMean`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=1'
'CREATE TABLE `Player` (
`PlayerID` int(10) unsigned NOT NULL DEFAULT ''0'',
`PlayerName` varchar(40) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerPrimaryClub` int(10) unsigned NOT NULL DEFAULT ''0'',
`PlayerAddress1` varchar(50) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerAddress2` varchar(50) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerCity` varchar(30) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerState` char(2) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerProvince` varchar(25) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerPostalCode` varchar(16) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerCountry` char(3) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerEmail` varchar(254) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerBirth` date NOT NULL DEFAULT ''0000-00-00'',
`PlayerDeceased` enum(''No'',''Yes'') COLLATE latin1_german1_ci NOT NULL DEFAULT ''No'',
`PlayerGender` char(1) COLLATE latin1_german1_ci NOT NULL DEFAULT '''',
`PlayerUSATT_ID` mediumint(8) unsigned NOT NULL DEFAULT ''0'',
`PlayerTTA_ID` mediumint(8) unsigned NOT NULL DEFAULT ''0'',
`PlayerSport` tinyint(3) unsigned NOT NULL,
`PlayerMean` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`PlayerStDev` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`PlayerLastEvent` int(10) unsigned NOT NULL DEFAULT ''0'',
`PlayerLastPlayed` date NOT NULL DEFAULT ''0000-00-00'',
`PlayerNotify` enum(''No'',''Yes'') COLLATE latin1_german1_ci NOT NULL DEFAULT ''No'',
`PlayerPassword` tinyblob NOT NULL,
`PlayerRevision` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`PlayerLastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`PlayerID`),
KEY `LastPlayedMean` (`PlayerLastPlayed`,`PlayerMean`),
KEY `CountryName` (`PlayerCountry`,`PlayerName`),
KEY `PostalCode` (`PlayerPostalCode`),
KEY `GenderName` (`PlayerGender`,`PlayerName`),
KEY `SportMean` (`PlayerSport`,`PlayerMean`),
KEY `Name` (`PlayerName`),
KEY `SportName` (`PlayerSport`,`PlayerName`),
KEY `ClubName` (`PlayerPrimaryClub`,`PlayerName`),
KEY `StateMean` (`PlayerState`,`PlayerMean`),
KEY `Birth` (`PlayerBirth`),
KEY `LastPlayedName` (`PlayerLastPlayed`,`PlayerName`),
KEY `StDev` (`PlayerStDev`),
KEY `CountryMean` (`PlayerCountry`,`PlayerMean`),
KEY `GenderMean` (`PlayerGender`,`PlayerMean`),
KEY `StateName` (`PlayerState`,`PlayerName`),
KEY `ClubMean` (`PlayerPrimaryClub`,`PlayerMean`),
KEY `Mean` (`PlayerMean`),
KEY `Rank` (`PlayerSport`,`PlayerGender`,`PlayerDeceased`,`PlayerMean`),
KEY `List` (`PlayerSport`,`PlayerDeceased`,`PlayerName`),
KEY `ProvinceMean` (`PlayerProvince`,`PlayerMean`),
KEY `ProvinceName` (`PlayerProvince`,`PlayerName`),
KEY `USATT_ID` (`PlayerUSATT_ID`),
KEY `TTA_ID` (`PlayerTTA_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=1'