March 2, 2012

How to run multiple sql queries using php without giving load on mysql server?

Question by aslamdoctor

I have a script that reads an excel sheet containing list of products. These are almost 10000 products. The script reads these products & compares them with the products inside mysql database, & checks

  • if the product is not available, then ADD IT (so I have put insert query for that)

  • if the product is already available, then UPDATE IT (so I have put update query for that)

Now the problem is, it creates a very heavy load on mysql server & it shows a message as “mysql server gone away..”.

I want to know is there a better method to do this excel sheet work without making load on mysql server?

Answer by Starx

Ok, here is quick thought

Instead of running the query, after every check, where its present or not, add on to your sql as long as you reach the end and then finally execute it.

Example

$query = ""; //creat a query container
if($present) {
    $query .= "UPDATE ....;"; //Remember the delimeter ";" symbol
} else {
    $query .= "INSERT ....;";
}
//Now, finally run it
$result = mysql_query($query);

Now, you make one query at the last part.


Update: Approach this the another way

Use the query to handle it.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Reference

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!