Php, MySql - Optimisation

manpreet Best Answer 2 years ago

I asked this on the general site, but they suggested I come here for ultimate optimisation. It really boils down to which is faster; doing a load of calculations in php with database stuff that is accessed once, or doing two database calls. (There are about 1000-1500 table entries, and I have to use the lot.)

Here is my original 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] 
                                                

                                                                                                        
                                                    
                                                
                                                

                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares
                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp