Update multiple rows Mysql PHP taking long time and crashes

Question

I want to update thousands of rows when the user click on a submit button, my function use another functions that calculates the field that I want to update. My problem is when I try to update a few rows like 5 or 10 it works fine but when I have like 20 rows or more, it takes a long time to execute the code or sometimes, it displays a lot of errors that I don't have. So can anyone help me please!

My code:

//----database connection
/* DATABASE CONFIGURATION */
define('DB_SERVER', 'localhost');
define('DB_PORT', '8090');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'db_qvt');

function getDB(){
    $dbhost=DB_SERVER;
    $dbport=DB_PORT;
    $dbuser=DB_USERNAME;
    $dbpass=DB_PASSWORD;
    $dbname=DB_DATABASE;
    // Check connection
    $dbConnection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
    mysqli_set_charset($dbConnection, "utf8");
     return $dbConnection;
}

//------------ update user final score--------------//
function update_user_final_score($login, $pass_user, $final_score){

  $sql_update="update users_answers set score_final = '$final_score' where login = '$login' and pass_user = '$pass_user'";
  $res = mysqli_query(getDB(), $sql_update);
  return $res;
}

//------------ calculate all users final scores--------------//
function update_all_users_final_score($login){
  $users_client = get_users_answers($login);
  for($i=0;$i<sizeof($users_client);$i++){
    update_user_final_score($users_client[$i]['login'], $users_client[$i]['pass_user'], calculate_user_final_score($users_client[$i]['login'], $users_client[$i]['pass_user']));
  }
}
//-- call the function
update_all_users_final_score($login);

Answer 1

You are using a loop for iterating over clients. This means that for every client you run a separate SQL query.

I believe you should create an SQL statement like this:

UPDATE users_answers
SET score_final = (case when login = 'X' then '1'
                        when login = 'Y' then '2'
                        when login = 'Z' then '3'
end);

This way you only have to run one query which should be a lot faster than running multiple queries.

The easiest way to create a query with multiple cases is to iterate over the client array, and add the cases ['when login="whatever" then "calculated_score"'] to the SQL statement string then pass the SQL command string to your mysql server.

Answer 2

I would say tha one of the problems here is getDB() function that most likely creates a new connection to a database every time it's called which is obviously would be slower than using a single connection.

Also, the problem could be caused by some specific mysql configuration option, innodb_flush_log_at_trx_commit.

Either way, the most robust and error-free method for the multiple inserts would be to use a prepared statement with all inserts wrapped in a transaction.

So the code would be

function update_user_final_score($db, $login, $pass_user, $final_score){

  $sql = "update users_answers set score_final=? where login=? and pass_user=?";
  $stmt = $b->prepare($sql);
  $stmt->bind_param("sss", $login, $pass_user, $final_score);
  $stmt->execute();
}

function update_all_users_final_score($db, $login){
  $users = get_users_answers($login);
  $db->begin_transaction();
  foreach($users as $client){
    $score = calculate_user_final_score($client['login'], $client['pass_user']);
    update_user_final_score($db, $client['login'], $client['pass_user'], $score);
  }
  $db->commit();
}

so it could be called like this

$db = getDB();
update_all_users_final_score($db, $login);

Regarding error messages you are getting, most likely it's due to wrong SQL syntax, and this issue would be resolved by the use of prepared statements as well.