PhP, MySql - Optimising Code

Question

I am not a great php coder (I come from C++). I am using php only for database entry.

I have a database with the following:

UserId (an unique int)
AsyncPointsAverage (float)
AsyncPointsAverageRank (a position based on the value immediately above)
AsyncPointsRecentAverage (float an average for the last 5 tests only)
AsyncPointsRecentAverageRank (a position based on the value immediately above)

There are about 1000-1500 entries in that table. Every morning and afternoon 5 people take a test which effects their overall average and recent average. (This is updated elsewhere, but not shown here.) After that is calculated for those 5 people, then the rankings of all 1000-1500 will be effected, so I have written the code below. Is it optimal?

The thing I am most concerned with is I am doing a MySql UPDATE about a 1000 times. Is that great? Should I be doing it another way? (Also feel free to optimise any other code in the function. As I say, I am from a C++ background, so do not really know the nuances of php.)

// Sorts by array entry 1
function ReRankCompareAverage($a, $b)
{
    if($a[1] == $b[1]) return 0;
    else return ($a[1] > $b[1] ? 1 : -1);
}
// Sorts by array entry 2
function ReRankCompareAverageRecent($a, $b)
{
    if($a[2] == $b[2]) return 0;
    else return ($a[2] > $b[2] ? 1 : -1);
}

function ReRank($db)
{
    $i = 0, $j = 0;
    $usersARR = null;

    $stmt = $db->prepare("SELECT UserId, AsyncPointsAverage, AsyncPointsRecentAverage FROM studenttable");
    $stmt->execute();
    if($stmt && isset($stmt) && $stmt->rowCount() > 0)
    {
        $i = 0;
        while(($row = $stmt->fetch(PDO::FETCH_ASSOC)))
        {
            $usersARR[$i][0] = intval($row['UserId']);
            $usersARR[$i][1] = floatval($row['AsyncPointsAverage']);
            $usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']);
            $i++;
         }
    }
    $stmt->closeCursor(); // mysql_free_result equivalent

    // The first pass of $j == 3 does the ranking by Average, filling position $usersARR[][3] with that rank
    // The second pass of $j == 4 does the ranking by AverageRecent, filling position $usersARR[][4] with that rank
    for($j = 3, $j <= 4; $j++)
    {
        $iCompare = $j == 3 ? 1 : 2;

        usort($usersARR, $j == 3 ? "ReRankCompareAverage" : "ReRankCompareAverageLast");
        $count = count($usersARR);
        if($count > 0)
        {
            // Start it off, with the person with the highest average is rank 1
            $usersARR[$count - 1][$j] = 1; // Position $j is filled with the rank
            // Now loop starting from the second one down
            for($i = $count - 2, $rank = 1; $i >= 0; $i--)
            {
                // Only change the rank if the next one down is strictly lower than the one above, otherwise will share the same rank
                if($usersARR[$i][$iCompare] < $usersARR[$i+1][$iCompare]) $rank = $count - $i; // Otherwise keep the same rank, because they are equal
                $usersARR[$count - 1][$j] = $rank;
            }
        }
     }

     // Now $usersARR is filled with the correct rankings, and they are asscoiated with $UserId
    // Now we must put all of these rankings into the database
    $count = count($usersARR);
    for($i = 0; $i < $count; $i++)
    {
         $stmt = $db->prepare("UPDATE studenttable SET AsyncPointsAverageRank=:AsyncPointsAverageRank, AsyncPointsRecentAverageRank=:AsyncPointsRecentAverageRank "
                        . "WHERE UserId=:UserId");
         $stmt->execute(array(':AsyncPointsAverageRank' => $usersARR[$i][3],
                        ':AsyncPointsRecentAverageRank' => $usersARR[$i][4],
                        ':UserId' => $usersARR[$i][0]));
    }
}

You can use transaction and do all the updates. I'm not sure if MyISAM supports transaction but InnoDb does. — frz3993
– frz3993, Commented Mar 23, 2016 at 14:34
Your code is injection safe, and running a thousand small update queries is no problem for any modern database server. I'd say you're fine. If you want further optimisation, you're on the wrong StackExchange site. :) — Joel Hinz
– Joel Hinz, Commented Mar 23, 2016 at 14:34
Didn't look into details for your problem since the way you're doing it seems fine but, just for "discussion purpose", if you want to avoid a thousand updates in your db, you should maybe consider another "ranking" system, like a column in your table that refers to the "previous" or the "next element". This way, your ranking update will only affect the "reclassed" items and neighbours... — Julo0sS
– Julo0sS, Commented Mar 23, 2016 at 14:35

Mark · Accepted Answer · 2016-03-23 16:02:52Z

4

How do you need to use the ranking? Maybe you store Ranks unnecessary? They might be easily calculated:

SELECT COUNT(*) 
FROM studenttable 
WHERE AsyncPointsAverage > $currentUserVariableAsyncPoints

To show TOP 10:

SELECT * FROM studenttable ORDER BY AsyncPointsAverage DESC LIMIT 0,10

etc.

EDIT:

To show complete ranking with position number you can either do it in PHP (you have it already - inside loop where you fetch rows just display $i++ variable). Or you can try with pure SQL (personally I like it better):

SET @rank=0; SELECT @rank := @rank +1 AS rank, UserId, AsyncPointsAverage
FROM studenttable
ORDER BY AsyncPointsAverage DESC

edited Mar 23, 2016 at 16:02

answered Mar 23, 2016 at 15:06

Mark

1,44718 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rewind Over a year ago

The students will be able to log on to the system and see their rankings, by scolling through pages (to see how they compare to everyone else). They can also log onto to their own personal page which will have their overall rank and recent rank. I thought calculating the rankings once after each test would be the best way to do it, rather than calculating it each time for the thousands of views by each student each day. (There are only 2 tests a day for just 5 students each test.)

Rewind Over a year ago

Will your ranking method give the same rank to equal averages? Eg say the 2nd and 3rd students have an average of 88%. They will both be ranked 2nd. Then the next person will be ranked 4th (ie leaving out rank 3rd entirely, because 2 people have second.)

Mark Over a year ago

So use the PHP approach of row numbering. Keep points from last fetched row, increment $i if points differ and update last fetched points.

Rewind Over a year ago

That is a shame, as your method looks very clever and slick.

Technoh · Accepted Answer · 2016-03-23 16:29:13Z

Just to expand on Mark's answer, you do not need to recalculate the rank every time you add a test result. It is certainly functional but it is not optimal. The best way is to calculate the rank when you display it. If you want to allow for students having the same result and the same rank you could always calculate the rank in PHP.

SQL:

SELECT
    UserId,
    AsyncPointsAverage,
    AsyncPointsAverageRank
FROM
    studenttable
ORDER BY
    AsyncPointsAverage DESC

PHP:

$stmt = $db->prepare("SEE ABOVE...");
$stmt->execute();

if( $stmt && isset( $stmt ) && $stmt->rowCount() ) {
    $rank = 1;
    $last_grade = -1;

    while( ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) ) {
        $usersARR[$i][0] = intval($row['UserId']);
        $usersARR[$i][1] = floatval($row['AsyncPointsAverage']);
        $usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']);

        if( $usersARR[$i][1] < $last_grade ) {
            $rank++;
        }

        $usersARR[$i][3] = $rank;

        $last_grade = $usersARR[$i][1];
    }
}

You just need to change the fields read and the ORDER BY field if you want to order by recent average instead.

Collectives™ on Stack Overflow

PhP, MySql - Optimising Code

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related