Optimizing SQL:
Our webhost recently pulled the plug on our PHP scripts for over-using CPU ([rant], I'd advice avoiding LunarPages like the plague. Worst user-treatment I've ever seen, they just yanked our entire website, email, FTP, control panel access, everything offline without warning.[/rant])
I was hoping someone here could give us some tips on how to optimize the SQL to reduce load (so we don't upset the NEXT web-host as well :) It's a fairly simple script, just used for downloading top-5 highscores. The highscores get hit at the end of every level, ~18K entries for the last 10 days).
The SQL/PHP code looks like this:
$query = "SELECT GameUser.Name, GameUser.Location, GameScore.Score
FROM `GameScore`,`GameUser`
WHERE GameUser.UserID = GameScore.UserID
AND GameScore.Level = '$Level'
AND GameScore.GameID = $GameID
ORDER BY GameScore.Score DESC LIMIT 5";
/*echo $query;*/
$result = mysql_query($query, $db) or die("@@Error@@ executing query: ".mysql_error());
while ($myrow = mysql_fetch_row($result))
{
printf("%s@%s@%d@ ", $myrow[0], $myrow[1], $myrow[2]);
}
The table looks like this
CREATE TABLE `GameScore` (
`Score` BIGINT,
`Level` TEXT,
`GameID` BIGINT,
`Date` DATETIME,
`VersionID` BIGINT,
`UserID` BIGINT NOT NULL ,
INDEX ( `UserID`)
)
CREATE TABLE `GameUser` (
`Name` TEXT,
`Location` TEXT,
`Created` DATETIME,
`UserID` BIGINT NOT NULL AUTO_INCREMENT,
INDEX ( `UserID` )
)
Specifically, they're complaining about the Select query. Is it the table-join that's getting the server down, or just bad use of indexing?
Thanks for any ideas!
Allan
