Is there any difference between a query that updates using WHERE IN () or individual queries

Is running an update using a WHERE pk开发者_开发知识库ey IN () is more optimal than individual update statements

update table_name set col='val' where primary_key in (..)

vs

update table_name set col='val' where primary_key = xx1
update table_name set col='val' where primary_key = xx2
...

There will be 1000s of updates on a table with millions of rows.

Yes, IN () is much faster as the query optimizer can do 1 pass of the key index, to update many rows in 1 hit. As long as there isn't a SELECT in the brackets, it will be faster.

As to how many id's to pack into the brackets, find out the max packet size for your deployment server, and work it out based on the longest an INT can be in base10 digits.

It's almost always better to execute fewer queries rather than more queries, because that gives the server the opportunity to optimize the operation. That said, I don't think any server will let you can pass millions of value arguments to an in() clause, so you might want to batch them up, updating, perhaps 50 at a time, or something like that.

I think IN will be faster, but it will be limited with number of entries. You will not be able to create 100K SQL in most cases. Time of parsing can also became significant, since database parse might be not optimized for the large SQLs. In the same time I would like to say that update of the primary key is inherently expensive operation. From some percentage of the change it will be faster to create new table with updated data, and then reindex it.