CODEWITHSUNDEEP
phpmysql
Jilu
Jilu

Reputation: 197

How to make my php code 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 ?

EDIT my codeV3 after improvement:

    $userList = getFromAPI();

      foreach ($userList as $userId){

        $db = dbConnect();


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



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

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

Upvotes: 3

Views: 500

Answers (3)

mingos
mingos

Reputation: 24542

You can use a single query for that:

INSERT INTO users (id, name)
VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Cecil')
ON DUPLICATE KEY UPDATE name = VALUES(name);

In a nutshell: you insert new rows, but if one already exists (the key is duplicated), it is updated instead. You can build your insert values in a loop so you end up with a single query instead of 4000+.

Read more here.

Upvotes: 3

W. Nowogrodzki
W. Nowogrodzki

Reputation: 21

First of all get fetching all users ids from database out of foreach lopp and buffer it in some variable. Should be better.

Upvotes: 2

Carl Kroeger Ihl
Carl Kroeger Ihl

Reputation: 341

Maybe try with putting $db = dbConnect(); outside of your foreach? I don't know if it is needed to open the connection in each cycle. It may be time consuming aswell.

Upvotes: 4

Related Questions