CODEWITHSUNDEEP
mysqloptimization
sreimer
sreimer

Reputation: 4998

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

Is running an update using a WHERE pkey 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.

Upvotes: 1

Views: 90

Answers (3)

David Gruzman
David Gruzman

Reputation: 8088

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.

Upvotes: 0

servermanfail
servermanfail

Reputation: 2538

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.

Upvotes: 1

Steve Jorgensen
Steve Jorgensen

Reputation: 12341

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.

Upvotes: 0

Related Questions