0

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.

  • 1
    You could use INSERT ... ON DUPLICATE KEY UPDATE to avoid sending 2 queries for each row. Combine that with inserting multiple rows per statement (100, 1000 rows per statement or even all 60K in one) and you'll eliminate the overhead. – ypercubeᵀᴹ Nov 20 '16 at 17:10
  • But how much i understand First i need to make insert, because i have few days when i receive file without any new records. – Romans Leonovs Nov 20 '16 at 18:10
  • I think i find out about lot of updates but i really dont know if its correct by in one query send CASE. for example UPDATE products SET Price = CASE product_id WHEN 1 THEN 20.00 ELSE barcode END; – Romans Leonovs Nov 20 '16 at 19:48
1

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
|improve this answer|||||
  • Sounds interesting. Im gonna try it thank you for answer :) – Romans Leonovs Nov 20 '16 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.