Update multiple rows or write to database

Question

If I have a script like this:

$sql = "SELECT * FROM table WHERE Status='0'"; $query = mysql_query($sql); while ($row = mysql_fetch_array($query)) $id = $row['ID']; //......... if($process->send()) { //after sent mysql_query("UPDATE table SET Status ='1' WHERE ID = '$id'"); } } 

Therefore, it will update every line when the process is complete. But if I have more than ten thousand records with Status='0' , the update will be slow.

So, is there a better way to update a record? I cannot update everything with a single request, since I need to know if each process is running or not.

Thanks.

Answer 1

Add all the successful ones to the array and just copy them all at once.

 if ($process->send()) { $done[] = $id; } 

and a little later:

 mysql_query('UPDATE table SET Status=1 WHERE ID IN ('.implode(',', $done).')'); 

Answer 2

Depending on the version of MySQL you are using, you might consider setting up a function that stores this procedure in such a way that you can compensate for this process in the database so that it does not delay your script.

Documentation for creating a procedure / creating a function: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Answer 3

If the UPDATE query is slow, you can use the LOW_PRIORITY keyword for the MyISAM storage engine:

http://dev.mysql.com/doc/refman/5.0/en/update.html

Example:

UPDATE LOW_PRIORITY SET Status = 1 WHERE id = someid;

Answer 4

You must make a stored procedure for this code. It works very fast.