SQL multiple updated rows with 1 connection

[drewbee]

Hello all, to summarize this i basically have a table that gets selected upon, then for each row that is returned needs to be updated.

 

IE

 

$query = mysql_query("SELECT id FROM tablename WHERE foo = bar");

 

while ($row = mysql_fetch_assoc($query))

{

    $update = mysql_query("UPDATE tablename SET column = 'blah' WHERE id = $row['id']");

}

 

This is for batch processing so the records are actually pulled in earlier, and then updated later on. (ID and new information is saved in an array though)

 

The batches will generally run between 25-50 records at atime, and obviously making 25-50 update statements in a few seconds is not deseriable.

 

What is the best way to optimize this? Perhaps using transactions and do 1 final commit at the end?

[ohdang888]

this looks like a cron job or something, or maybe even something just to fix the table once.

 

so its not like its slowing a user down..

also, i did that same script once with a table that can 500 rows.... and it didn't take long at all.

[drewbee]

Its from a script that will be running that is based launched off of user actions. At any time, this very script could be running 100's of times. Obviously there is some scability issues with that current setup. While I have faith that it will not have any hicups with 10 users running it, the amount that it potentially could be running at is the current user base that I have. This is why I am seeing an issue with this

[discomatt]

mysqli_multi_query()

[drewbee]

^^^^^ Beautiful

 

What is mysqli ? It can be used in conjunction with the old stuff, right? mysql_query etc.

[drewbee]

Ok. After doing some tests and reading up on some benchmarking... mysqli_multi_query actually ran slower then looping through and updating each row individually. Seems kinda pointless?

 

I havn't got to test this out yet, but how do transactions usually operate?

 

{start transaction}

 

  {for loop 0 > 24; index = x}

    {update foo = bar, seed = hash where id = loop{x}}

  {/for loop}

 

{end transaction; commit}

 

Does this end up running as 25 seperate queries, or is it grouped and sent to mysql all at once?