Is this so bad when using MySQL queries in PHP?

Question

I need to update a lot of rows, per a user request. It is a site with products.

I could...

• Delete all old rows for that product, then loop through string building a new INSERT query. This however will lose all data if the INSERT fails.
• Perform an UPDATE through each loop. This loop currently iterates over 8 items, but in the future it may get up to 15. This many UPDATEs doesn't sound like too good an idea.
• Change DB Schema, and add an auto_increment Id to the rows. Then first do a SELECT, get all old rows ids in a variable, perform one INSERT, and then a DELETE WHERE IN SET.

What is the usual practice here?

Thanks

Answer 1

15 UPDATEs is really not all that many. If you were talking about 15 hundred then perhaps you'd have to think about your design a bit more...

Answer 2

Just do the updates. In a transaction if you need to. 15 updates is peanuts, unless you're doing it on every page access or something.

You don't want to be deleting/re-inserting rows just to avoid extra queries. And you won't be able to, if you ever want to have a foreign key referencing the table you're updating.

Almost certainly a premature optimisation.

Answer 3

The best practice is probably to use stored procedures.
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html
It would be only one command on PHP side. The rest would be implemented on MySQL sever.

You can also try to execute few queries at once using mysqli::multi-query.
http://php.net/manual/en/mysqli.multi-query.php