In my experience the fastest way to update a large number of rows to unique values that can't be calculated (assuming that's why you think you need multiple queries?
) Is to use bulk copy https://github.com/denisenkom/go-mssqldb/blob/master/examples/bulk/I'm not sure there is a mysql equivalent. You bulk copy in a set of rows with the primary key and the new value into a temp table then do an update statement with an inner join to the new table then delete the temp table.
Mssql bcp can do hundreds of thousands of inserts in less than a second Your problem is partially that you're changing the primary key very incrementally and doing it with transactions, so you're incurring a lot of cost in practically every place you could.
In my experience this is one of the tradeoffs between SQL and No SQL.
As long as you feel you must update one by one the problem will exist, as each record no matter the concurrency will need to lock the table while doing work.
Without the reasoning for your "I have to clause" no one can really help.
If you don’t care about any records in the table, as mentioned TRUNCATE TABLE foo will work. If the table has a lot of indices and foreign keys, that might slow things down.
If it’s heavily used, you may not be able to get locks to delete, or once you do get a lock block other stuff.