From e8dc0c3aaeca2e63bf966eb9e0f9be609fe2aeb3 Mon Sep 17 00:00:00 2001 From: BlueRaja Date: Wed, 20 Mar 2013 02:03:34 -0500 Subject: A ton of optimizations to the queries, along with the ability to run DB updates in chunks of 1 month --- includes/playerStats.php | 135 +++++++++++++++++++++++++++++++---------------- 1 file changed, 90 insertions(+), 45 deletions(-) diff --git a/includes/playerStats.php b/includes/playerStats.php index 11c103a..5640dc7 100644 --- a/includes/playerStats.php +++ b/includes/playerStats.php @@ -5,24 +5,28 @@ include_once('constants.php'); /** * Sets a flag on all solutions which are the high score (first to tie the best length) */ -function setHighScoreFlags($mapExpireTime) +function setHighScoreFlags($mapExpireStartRange, $mapExpireEndRange = "NOW()") { - $mapExpireTime = mysql_escape_string($mapExpireTime); + $mapExpireStartRange = quoteSQLDateString($mapExpireStartRange); + $mapExpireEndRange = quoteSQLDateString($mapExpireEndRange); $sql1 = " - UPDATE solutions AS s1 - INNER JOIN mapOfTheDay ON s1.mapID = mapOfTheDay.mapID - SET s1.isHighScore = 1 - WHERE mapOfTheDay.mapExpireTime <= NOW() - AND mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND s1.ID = + UPDATE solutions + INNER JOIN ( - SELECT s2.ID - FROM (SELECT ID, mapID, moves, dateModified FROM solutions) AS s2 - WHERE s2.mapID = s1.mapID - ORDER BY moves DESC, dateModified ASC - LIMIT 1 - ) + SELECT a1.id FROM solutions AS a1 + INNER JOIN mapOfTheDay ON a1.mapID = mapOfTheDay.mapID + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange + AND NOT EXISTS + ( + -- Only select rows which have highest score for map, or tied for highest with earliest date, or tied for date too with earliest ID + SELECT 0 FROM solutions AS a2 + WHERE a2.mapID = a1.mapID AND + (a2.moves > a1.moves OR (a2.moves = a1.moves AND (a2.dateModified < a1.dateModified OR (a2.dateModified = a1.dateModified AND a2.ID > a1.ID)))) + ) + ) tmp ON tmp.id = solutions.id + SET solutions.isHighScore = 1 "; $sql2 = " @@ -33,12 +37,11 @@ function setHighScoreFlags($mapExpireTime) FROM solutions AS s2 WHERE isHighScore = 1 GROUP BY mapID - ) AS maxMoves ON maxMoves.mapID = solutions.mapID + ) AS maxMoves ON maxMoves.mapID = solutions.mapID AND solutions.moves = maxMoves.moves INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID SET solutions.isTiedForHighScore = 1 - WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() - AND solutions.moves = maxMoves.moves + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange "; mysql_query($sql1); @@ -50,9 +53,10 @@ function setHighScoreFlags($mapExpireTime) * @param $mapExpireTime The earliest mapExpireTime to count from. Use SQL date-string format. * Pass in today's date, for example, for all maps that expired at midnight today */ -function calculateMapsChampionPointWorth($mapExpireTime) +function calculateMapsChampionPointWorth($mapExpireStartRange, $mapExpireEndRange = "NOW()") { - $mapExpireTime = mysql_escape_string($mapExpireTime); + $mapExpireStartRange = quoteSQLDateString($mapExpireStartRange); + $mapExpireEndRange = quoteSQLDateString($mapExpireEndRange); //We take the worth of the map, multiply by the number of people who tied the high score, then divide by the //total number of people who attempted the map (minus 1) @@ -78,8 +82,8 @@ function calculateMapsChampionPointWorth($mapExpireTime) FROM solutions WHERE solutions.mapID = mapOfTheDay.mapID )) - WHERE mapExpireTime >= '$mapExpireTime' - AND mapExpireTime <= NOW() + WHERE mapExpireTime >= $mapExpireStartRange + AND mapExpireTime <= $mapExpireEndRange "; mysql_query($sql); @@ -92,9 +96,10 @@ function calculateMapsChampionPointWorth($mapExpireTime) * @param $mapExpireTime The earliest mapExpireTime to count from. Use SQL date-string format. * Pass in today's date, for example, for all maps that expired at midnight today */ -function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) +function addPlayerChampionPointsForMapsOfTheDay($mapExpireStartRange, $mapExpireEndRange = "NOW()") { - $mapExpireTime = mysql_escape_string($mapExpireTime); + $mapExpireStartRange = quoteSQLDateString($mapExpireStartRange); + $mapExpireEndRange = quoteSQLDateString($mapExpireEndRange); //Weird syntax of these queries is necessary due to a bug in MySQL with updating a table and selecting a table //in the same query. See http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ @@ -103,8 +108,8 @@ function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) UPDATE solutions INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID SET solutions.championPointsWorth = mapOfTheDay.championPointsWorth - WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange AND mapOfTheDay.mapType <> 5 AND solutions.isTiedForHighScore "; @@ -129,8 +134,8 @@ function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) FROM (SELECT * FROM solutions) AS usersSolutions WHERE usersSolutions.ID = solutions.ID ) - WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange AND mapOfTheDay.mapType = 5 "; @@ -140,8 +145,8 @@ function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID SET solutions.championPointsWorth = ".CP_POINTS_FOR_ATTEMPTING." WHERE solutions.championPointsWorth = 0 - AND mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() + AND mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange "; $sql4 = " @@ -150,8 +155,8 @@ function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID SET solutions.championPointsWorth = solutions.championPointsWorth * (1 + ".CP_EXTRA_PERCENT_FOR_FIRST.") WHERE solutions.isHighScore = 1 - AND mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() + AND mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange "; //TODO: Points for doing challenges too @@ -159,12 +164,16 @@ function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime) $sql5 = " -- Simply re-sum all the points UPDATE users - SET championPoints = + INNER JOIN ( - SELECT SUM(championPointsWorth) + SELECT SUM(solutions.championPointsWorth) AS championPointsWorth, solutions.userID FROM solutions - WHERE solutions.userID = users.ID - ) + INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange + GROUP BY solutions.userID + ) AS newPointsEarned ON users.ID = newPointsEarned.userID + SET users.championPoints = users.championPoints + newPointsEarned.championPointsWorth "; mysql_query($sql1); @@ -202,9 +211,10 @@ function clearAllStatsBeforeRecalculation() /** * Calculates players' total wins, draws, moves, and mazes, adding from the given date */ -function addOtherStats($mapExpireTime) +function addOtherStats($mapExpireStartRange, $mapExpireEndRange = "NOW()") { - $mapExpireTime = mysql_escape_string($mapExpireTime); + $mapExpireStartRange = quoteSQLDateString($mapExpireStartRange); + $mapExpireEndRange = quoteSQLDateString($mapExpireEndRange); $sql = " -- Add wins, ties, mazes, and moves all at once @@ -218,8 +228,8 @@ function addOtherStats($mapExpireTime) COUNT(*) AS totalMazes FROM solutions INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID - WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime' - AND mapOfTheDay.mapExpireTime <= NOW() + WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange + AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange GROUP BY solutions.userID ) AS newPoints ON users.ID = newPoints.userID SET users.totalWins = users.totalWins + newPoints.totalWins, @@ -231,6 +241,18 @@ function addOtherStats($mapExpireTime) mysql_query($sql); } +/** + * Adds quotes around an SQL date string and escapes it, if necessary + */ +function quoteSQLDateString($dateString) +{ + if($dateString == "NOW()") + { + return $dateString; + } + return "'" . mysql_escape_string($dateString) . "'"; +} + /** * Calculates the worth of the maps for yesterday, and distributes points for them. Also adds wins and ties to * the correct players @@ -269,10 +291,33 @@ function addStatsForDateTimeInternal($dateTime) function recalculateStatsForAllPlayers() { clearAllStatsBeforeRecalculation(); - setHighScoreFlags(STATS_EARLIEST_DATE); - calculateMapsChampionPointWorth(STATS_EARLIEST_DATE); - addPlayerChampionPointsForMapsOfTheDay(STATS_EARLIEST_DATE); - //TODO: Champion points for challenges! - addOtherStats(STATS_EARLIEST_DATE); + + $startDate = new DateTime(STATS_EARLIEST_DATE); + $currentEnd = $startDate; + $currentEndStr = ""; + $now = new DateTime("now"); + + while($currentEndStr != "NOW()") + { + $currentStart = new DateTime($currentEnd->format("Y-m-d")); + $currentStart->add(new DateInterval("PT1S")); //Add one second to start time, since it's inclusive + + $currentEnd = new DateTime($currentEnd->format("Y-m-d")); + $currentEnd->add(new DateInterval("P1M")); //Run queries for one month at a time + + $currentStartStr = $currentStart->format("Y-m-d H:i:s"); + $currentEndStr = $currentEnd->format("Y-m-d H:i:s"); + + if($currentEnd > $now) + { + $currentEndStr = "NOW()"; + } + + setHighScoreFlags($currentStartStr, $currentEndStr); + calculateMapsChampionPointWorth($currentStartStr, $currentEndStr); + addPlayerChampionPointsForMapsOfTheDay($currentStartStr, $currentEndStr); + //TODO: Champion points for challenges! + addOtherStats($currentStartStr, $currentEndStr); + } } ?> -- cgit v1.2.3