Jump to content

Combine thousands of queries into one?


hyeteck

Recommended Posts

Hi,

 

is there a way to combine 17,000 UPDATE queries into 1 query?  I have a php script that runs through a while loop and updates a price field for 17,000 rows, hence 17,000 queries.  Since i'm on a share host, the MySQL server is on a different machine so sending 17,000 queries takes time.  Is there a way to combine them together?  Here is my script.

 

function update_prices()
{
$pri = array(0,  10, 25, 50, 100,200, 300, 400, 500, 600, 700,800, 900, 1000);
$f   = array(1.8,1.7,1.6,1.5,1.3,1.09,1.08,1.13,1.12,1.11,1.1,1.09,1.08,1.07);

$file = "items.txt";
$contents = file($file);
if(count($contents)>1000)
{
	foreach($contents as $buffer)
	{
		$chunks = explode("	", $buffer);
		$prodId = $chunks[0];
		$prodPrice = $chunks[1];
		$prodPriceW = $prodPrice;
		$prodMap = $chunks[2];
		$prodQuant = $chunks[3];

		if($prodPrice==0)
		{
			$result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error());
		}
		else
		{				
			$c=14;
			while ($pri[--$c] > $prodPrice);
			$prodPrice *= $f[$c];
			$prodPrice = floor($prodPrice);
			$prodPrice += 0.99;

			print $prodId . "\n";
			$query = "UPDATE products SET pPrice= IF(pFreight = 1,'$prodPrice + 90','$prodPrice'), pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'";
			$result = mysql_query($query) or die(mysql_error());
		} // end of prodPrice == 0 else
	} // end of foreach loop
} // end of count > 1000
} // end of function

 

thanks

Link to comment
Share on other sites

Just in general, each different value would need a different UPDATE query. If you could pre-process the data so that you know which id's will be updated to the same values, you could produce an update query with a WHERE clause that has a list of id's that would be updated all at once.

 

Also, does a lot of your data change? The way the code is now, an update query is executed for each row. If the data values are the same, mysql does not actually do the write part of the UPDATE, but it still must do the read part of the UPDATE. You might be able to read all the rows from the database into an array and then do a comparison to find out what rows need to be updated, then simply execute the update query for only the rows that you need to change. This could reduce the time if only a fraction of the rows are changed, but if the quantity changes in most of the rows so that they all need to be updated anyway, it won't help.

 

Edit: You might actually be able to do a LOAD DATA LOCAL INFILE statement with the correct ignore/overwrite options to get this done batch mode. This might require that the data file be pre-processed by php and written out in a different format first with the price already figured.

Link to comment
Share on other sites

All the IDs have different values so trying to group together the IDs would not work.

 

I like the idea of checking which values need to be changed to limit the number of queries that way.  What i really like most is your idea of LOAD DATA INFILE.  Pre-processing the data file would not be an issue, i just gotta figure out the format the data needs to be in.

 

looking here http://dev.mysql.com/doc/refman/5.0/en/load-data.html i don't see information about using UPDATE in the data infile.

Link to comment
Share on other sites

Load data infile will be way quicker I would imagine.

The other option is to use prepared statements, although I don't believe the old mysql_* functions support this so you'd have to use PDO or mysqli_* instead.

 

Curiously however, I would ask why are you getting a file with ALL prices updated. Surely the supplied file should contain the latest price changes? Or is this exported from some other system? Either way it seems like a nightly batch job of a sort, for which LOAD DATA INFILE is going to be your best option.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.