I've always used simple sql update statements inside a loop when updating multiple records. This means the sql is quick but the database is connected to multiple time. Lately I've been wondering if it would be much better to use a loop to build up a sql statement that does multiple updates in one go.
Does it make much of a difference? I'm using PHP 5.6 & MySql.
Thank you.
The best solution would is to avoid loops altogether.
If you absolutely need to use a loop, you better do it in the database side, as this will eliminate the overhead related to the PHP/database communication.
As documented under Optimizing UPDATE Statements:
Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.
You may also find the page on Optimizing INSERT Statements helpful and informative.
To summarise, in the most general terms: connecting once and sending a single UPDATE statement is better than connecting once and sending multiple UPDATE statements, which in turn is better than connecting multiple times to send a single UPDATE statement on each connection.
Beyond that, there are a host of techniques that can be harnessed to optimise further—but we would need to know the specific configuration and requirements at hand.
