MySQL Multiple Check and Update Queries

MySQLPHP

i have question about checking and updating.
The problem is that i create a very basic mysql select and update , and
two times in day i get file where php reads it. In that file have more than 14k-60k records. Database checks if that id exists then make update for 2 columns but if that record doesnt exist then insert.

foreach($product_list as $id => $var) {if(mysql_num_rows(mysql_query("SELECT product_id FROM products WHERE product_id = $id")) == 0) { // insert}else{//update}}

problem is that, that script is taking very much resources and execute very long.
Is it possible to speed up this process or somehow make it in few queries? I understand that there is going more than 60k queries in this code.
P.s this is simple example with mysql but in real code im using PDO but system is like the same as this code.

Best Answer

I can suggest little more longer method, but work fine:

  1. Load file into table, for example TableB (original in this case TableA), TableB has only PK - id

  2. Update - before INSERT

    UPDATE TableA t1 INNER JOIN TableB t2 ON t1.id = t2.id

  3. Insert

    INSERT INTO TableA 
SELECT * 
FROM TableB t1 
WHERE NOT EXISTS 
      (SELECT id FROM TableA t2 WHERE t2.id = t1.id)

I prefer this way, because:

  • it fast
  • it flexible - steps in-dependent and logic for UPDATE/INSERT could be changed separately
Related Question