A ton of optimizations to the queries, along with the ability to run DB updates in chunks of 1 month

1 files 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,
@@ -232,6 +242,18 @@ function addOtherStats($mapExpireTime)
 }
 
 /**
+ * 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);
+	}
 }
 ?>