PHP MySQL make insert and update large amount of users faster I'am using php on server side to manage data with MySQL. I have to request a API that gives me an list of users. I need to check for each user if he is in the database. If yes, I update his information. If not, I insert him in the data base.

The issue is that there is more than 2000+ users each times and my code in PHP is really slow (sometimes I get 504 Gateway Time-out). We will have even more users very soon.

How can I make my code faster ? Is Php ok if there is more users?

    $userList = getFromAPI();

      foreach ($userList as $userId){

        $db = dbConnect();

  $query = $db-&gt;prepare(
      "SELECT openid FROM USERS WHERE openid = :openid"
    );

  $query-&gt;execute([
    "openid"=&gt;$openId,
    ]);

    $res = $query-&gt;fetch(PDO::FETCH_ASSOC);

  $tagList = implode(",", $user["tagid_list"]);

        if (!empty($res)) {

          $query = $db-&gt;prepare(
      "INSERT INTO USERS(id, name, group) VALUES(:id, :name, :group)
       ON DUPLICATE KEY UPDATE name=:name, group=:group"
          );

          $query-&gt;execute([
        "id"=&gt;$id,
        "name"=&gt;$name,
        "group"=&gt;$group
          ]);
        }

     }
Well, the key point would be to wrap all your inserts in a transaction to make them dramatically faster.

Other points of less significance but rather a matter of sanity are:


connect once
prepare once and also make sure you are using a non-emulated prepared statement
get rid of the useless SELECT


So it should be like 

$db = dbConnect();
$query = $db-&gt;prepare(
  "INSERT INTO USERS(id, name, `group`) VALUES(:id, :name, :group)
   ON DUPLICATE KEY UPDATE name=values(name), group=values(`group`)"
);

$userList = getFromAPI();
$db-&gt;beginTransaction();
foreach ($userList as $userId){
    // assuming you have a code to define these variables 
    $query-&gt;execute([
    "id"=&gt;$id,
    "name"=&gt;$name,
    "group"=&gt;$group
      ]);
}
$db-&gt;commit();