In MySQL is there a limit to the number of keys in a IN() clause?
I have a PHP program that does a SELECT and then updates some of the values based on an algorithm.
Rather than updating one row at a time
UPDATE example_table
SET COLUMN_A = 1
WHERE primary_key_column = 10;
I was thinking of doing many updates at once like
UPDATE example_table
SET COLUMN_A = 1
WHERE primary_key_column IN(1,2,3,4,5);
The SELECTS are pulling in 100,000 rows at a time and to start with every single one of them may need to be updated, am I going to run into a limit of values in the IN() clause?
2 answers
According to the documentation for the MySQL IN function:
The number of values in the IN() list is only limited by the max_allowed_packet value.
The default value for it is 67108864.
So, you should be able to squeeze quite a big number of identifiers, but you should definitely try it out to see how it behaves under a normal server load.
I am not sure how MySQL sees such queries though (I only have experience with such queries in MSSQL where it can complain that they are too complex).
0 comments
I'll readily admit I'm not too familiar with MySQL specifically, but personally, I would try to avoid listing all the primary key values in an ad-hoc query.
What I would rather do personally is to run a separate query to select the rows to update, and then include a condition that the rows to be updated are those that exist in that set of rows.
Something not entirely dissimilar to
UPDATE example_table
SET column_A = 1
WHERE primary_key_column IN (
SELECT primary_key_column
FROM example_table
WHERE <bunch of complex conditions>
)
Of course, in this particular case, you could just as well stick the WHERE clause from the subquery in the UPDATE statement itself, but that gets somewhat more complex if the query to find the rows to update is more complex than a plain SELECT from the same table that you're updating; say, it's actually selecting from a different table, with its own joins, maybe a union or two, and so on.
If you need to use the same set of key values multiple times and the query to find them is non-trivial, stick them into a temporary table that you SELECT from (or join against) instead, and put all of that into a stored procedure.
1 comment
So this way will lock up the DB for a while, and avoiding the locks is the whole point of using PHP and doing it in sections
3 comments
Another alternative is to try and set up all the SQL statements as a single transaction. Did you look into this? — ghost-in-the-zsh 2 months ago
@ghost-in-the-zsh Considering that OP said in a comment to my answer that the point is to not "lock up the DB for a while", I doubt doing it in a transaction would meet the (originally unstated) requirement. — Canina 2 months ago
@Canina I saw it. In MySQL, if you use InnoDB as the storage engine, then the only things getting locked during write operations are the rows being updated, not the tables themselves as a whole like MyISAM does. (And no one should be using MyISAM as the storage engine nowadays, since it's not even ACID-compliant.) — ghost-in-the-zsh 2 months ago