how to optimize... [900 queries] help

[tripod]

Hi, i have a problem...
What is the best method of update 900 records ???

  for($i=0; $i<900; $i++) {
  	 $cc=3;
  	 $q="UPDATE `bc_segments` SET `$k` = '$cc' WHERE `IDST` = $cur LIMIT 1;";
                 mysql_query($q);
}

This method is too slowly🙁 (10minitz)...

when i paste into phpmyadmin sql tab - big query:
"UPDATE bc_segments SET 1 = '3' WHERE IDST =61 LIMIT 1;
UPDATE bc_segments SET 2 = '3' WHERE IDST =61 LIMIT 1;
UPDATE bc_segments SET 3 = '3' WHERE IDST =61 LIMIT 1;
.
.
. etc - 900 times"

it is updating in 11 seconds!!!
so my question is: how write in php this same method of phpmyadmin?

PLZ HELP!

[tunage]

Your executing a mysql query each time you go through the loop - creating a multiple update query and then executing it after the loop should fix it.

I think this should work ..

$query="";

for($i=0; $i<900; $i++) { 

$cc=3; 
$q="UPDATE `bc_segments` SET `$k` = '$cc' WHERE `IDST` = $cur LIMIT 1; "; 
$query .= $q;

}  

mysql_query($query); 

[laserlight]

hmm... but your example doesnt make sense. It loops 900 times updating the same record.

If you can use transactions, wrap the queries in a transaction. Otherwise, you really have to give a better example.

[tripod]

Hey - tunage ur method is bad bcuz MySQL server have a protection: "Queries with ; queries" doesnt work.!!! [dev.mysql.com]; so when i pasting that big string into phpMyAdmin - its works very good!! but from php level its doesnt work.

Laserlight - i tried with transactions:

mysql_query("BEGIN WORK;");

  for($i=0; $i<$cols*$rows; $i++) {
  	$cc=$_POST['d'.$i];
  	if($k==31) { $k=1; }
  	if($i<$f) {    

  	   $q="UPDATE `bc_segments` SET `$k` = '$cc' WHERE `IDST` =$cur; ";

  mysql_query($q);
    }
else { ++$cur; 
       $f=$f+30;
  	   $q="UPDATE `bc_segments` SET `$k` = '$cc' WHERE `IDST` =$cur; ";
  	   mysql_query($q);
}
   $k++;


  }
  mysql_query("COMMIT;");

but it isnt work good 🙁 ...

[bastien]

I don't get the table structure, can you post that? and what you are trying to update...the info you have givin is too vague

[laserlight]

You do need to post a better explanation and example, and provide the relavent database schema, as bastien mentioned.

Using some guesswork, from what I see, this would be your code in simplified form:

//assume $cur has been initialised
mysql_query("BEGIN");
$num_iters = $cols * $rows;
for ($i = 0; $i < $num_iters; ++$i) {
	$cc = $_POST['d' . $i];
	$k = ($i % 30) + 1;
	mysql_query("UPDATE `bc_segments` SET `$k`='$cc' WHERE `IDST`=$cur");
	if ($k == 30) {
		++$cur;
	}
}
mysql_query("COMMIT");

What you can try is to update entire rows at once. There will be 30 columns per row, so you can try:

//assume $cur has been initialised
$i = 0;
$i_max = $cols * $rows;
while ($i < $i_max) {
	$pairs = array();
	$j = $i;
	$i += 30;
	while ($j < $i);
		$cc = $_POST['d' . $j];
		++$j;
		$pairs[] = "`$j`='$cc'";
	}
	mysql_query("UPDATE `bc_segments` SET " . implode(',', $pairs) . " WHERE `IDST`=$cur");
	++$cur;
}

This should reduce the number of queries by a factor of 30, assuming it works. If you can use them, transactions would still be a good choice.

but it isnt work good

How does it not work? Still too slow, or fails? The use of transactions may not be available.