what is more efficient:
START TRANSACTION
UPDATE mytable SET foo = 'bar' WHERE (col1 = 813242) AND (col2 = 25343);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 312643) AND (col2 = 8353);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 843564) AND (col2 = 41233);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 321312) AND (col2 = 5325);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 554235) AND (col2 = 6321);
... x 10,000 times or more
COMMIT;
or
UPDATE mytable SET foo = 'bar' WHERE
((col1 = 16344) AND (col2 = 5456)) OR
((col1 = 42134) AND (col2 = 5436)) OR
((col1 = 84563) AND (col2 = 2321)) OR
((col1 = 43216) AND (col2 = 4267)) OR
((col1 = 53248) AND (col2 = 6234)) OR
... x 10,000 times or more
Assuming I have UNIQUE index on (col1,col2)
So my guess is 1st option is nice because of index but it's split into multiple queries, 2nd option is nice because it's only one query but on another hand it does full table scan
this is EXPLAIN when not using OR:
type: ref, possible_keys: myindex_UNIQUE, key: myindex_UNIQUE, ref: const
this is EXPLAIN when using OR:
type: ALL, possible_keys: myindex_UNIQUE, key: null, ref: null
and is there any limit for query WHERE clause?
I aim for max speed
Based on the conversation in the comments, a possible solution:
If you have a large table and a list of values to identify the rows to update, you can create a helper table for the list of values.
Based on the example in the question, the table could be something like this:
CREATE TABLE mytable_operation (
col1 INT
, col2 INT
) ENGINE = MEMORY;
Please note, that the create statement contains the ENGINE = MEMORY hint, so the table will be stored in the memory instead of the disk.
Load the values into this table prior the update.
After all values are loaded into the helper table, you can use the following query to update the values in the production table.
UPDATE
mytable
SET
foo = 'bar'
WHERE
EXISTS (SELECT 1 FROM mytable_operation MO WHERE mytable.col1 = MO.col1 AND mytable.col2 = MO.col2)
Of course, you can use any DML statements to manipulate the production data. (UPDATE with joins, DELETE, INSERT..ON DUPLICATE KEY, etc)
When you finished the data manipulation, you can truncate or drop the helper table.
If the amount of rows are slightly larger in the production table, this solution could be faster than the solutions in the question.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句