drewbee Posted May 2, 2008 Share Posted May 2, 2008 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? Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/ Share on other sites More sharing options...
ohdang888 Posted May 2, 2008 Share Posted May 2, 2008 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. Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/#findComment-531398 Share on other sites More sharing options...
drewbee Posted May 2, 2008 Author Share Posted May 2, 2008 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 Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/#findComment-531407 Share on other sites More sharing options...
discomatt Posted May 2, 2008 Share Posted May 2, 2008 mysqli_multi_query() Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/#findComment-531408 Share on other sites More sharing options...
drewbee Posted May 2, 2008 Author Share Posted May 2, 2008 ^^^^^ Beautiful What is mysqli ? It can be used in conjunction with the old stuff, right? mysql_query etc. Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/#findComment-531415 Share on other sites More sharing options...
drewbee Posted May 5, 2008 Author Share Posted May 5, 2008 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? Link to comment https://forums.phpfreaks.com/topic/103797-sql-multiple-updated-rows-with-1-connection/#findComment-533473 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.