MySQL Number of Items Within "In Clause"

MySQL number of items within in clause

Starting from a certain number, the IN tables are faster.

MySQL has something inside its code that makes building a range over a large number of constant values slower than doing the same in a nested loop.

See this article in my blog for performance details:

• Passing parameters in MySQL: IN list vs. temporary table

MySQL IN clause: max number of arguments

You can also have the IN clause take the results of a query, such as:

SELECT * FROM table1 
WHERE table1.id IN 
    (
   SELECT id from table2
    )

That way, you don't need to generate a text string with all the possible values.

In mysql, you should be able to put as many values in the IN clause as you want, only constrained by the value of "max_allowed_packet".

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

MySQL IN condition limit

No there isn't, check the manual about the IN function:

The number of values in the IN list is only limited by the max_allowed_packet value.

Is there a Limit on MySQL IN() list

IN clause and max_allowed_packet

There's no defined limit on the number of items within IN (...).

However, the length of the SQL string itself (in bytes) is limited by the value of the max_allowed_packet system variable.

Here an excerpt from the MySQL documentation:

The number of values in the IN list is only limited by the max_allowed_packet value.

The default value of max_allowed_packet is 4 MB, so unless you had changed this value, it's extremely unlikely that this is the problem.
Besides, hitting this limit would have caused an error rather than wrong results.

---

addslashes

It's really not a good idea to use addslashes to build MySQL string literals.
Consider using mysqli_real_escape_string for this purpose.

---

A suggestion

Could you please do the following to analyze the issue.
Build a loop over $part_names, and execute the very same statement (incl. implode) - but for just one name in every iteration. Then check whether every iteration returns a non-empty result.

This way you would get the names which aren't returned and it can give you some clues.

Another possible outcome would be that all names are returned - in this case the problem could indeed be with the number of items within the IN clause.

MySQL: is there a limit to the entries of an IN clause?

From the manual:

The number of values in the IN list is only limited by the max_allowed_packet value.

MySQL number of items within in clause

Starting from a certain number, the IN tables are faster.

MySQL has something inside its code that makes building a range over a large number of constant values slower than doing the same in a nested loop.

See this article in my blog for performance details:

• Passing parameters in MySQL: IN list vs. temporary table

Mysql query with IN clause and limit for each item

You need the help of MySQL user defined variables

SELECT 
*
FROM 
(
    SELECT
        X.*,
        IF(@sameClass = class_id, @rn := @rn + 1,
             IF(@sameClass := class_id, @rn := 1, @rn := 1)
        ) AS rank
    FROM    table_x AS X
    CROSS JOIN (SELECT @sameClass := 0, @rn := 1 ) AS var
    WHERE   class_id IN (1, 2, 3) 
    ORDER BY class_id, time_x DESC
) AS t
WHERE t.rank <= 15
ORDER BY t.class_id, t.rank

In your case LIMIT 15 actually restricts the result set to contain at most 15 records which is not what you wanted.

What defines upper limit to the number of items in a MySQL WHERE clause?

That would be the size of the query packet, usually in the order of the megabytes.

With thousands of values, perhaps a temporary table to be joined could be advantageous. I've never tested this, though.

Update: the answer has been already given, and also the performance question has been answered. Placing the items in a temporary tables is better as soon as the number of the items grows above a couple dozens.

Also, this second solution might be more easily ported to other RDBMS (or MySQL configurations!) with more stringent limits. Imagine needing a 8M packet size and the hosting will only supply, say, 4.

How number of items in IN clause relates to index usage

When you use the IN() predicate, MySQL would have to analyze the index for each value in your list, estimating the benefit of using the index. When you use long lists of values, calculating the optimizer's estimate becomes expensive, even before executing the query.

In MySQL 5.6, they established a threshold so a list of 10 or more items in an IN() predicate skipped the index-dive-per-value work, and just guessed at the value of using the index based on previously collected stats about the index. This is documented here: https://dev.mysql.com/doc/refman/5.6/en/range-optimization.html in the subsection "Equality Range Optimization of Many-Valued Comparisons."

You can tune the threshold with the variable eq_range_index_dive_limit. In MySQL 5.6, the default is 10. In MySQL 5.7, they realized that the default of 10 was too small, so they increased the default value to 200. You could change this variable to 200 to be like the MySQL 5.7 behavior.

I notice you are using RDS. The default on RDS is sometimes different from the default in stock MySQL, so the default might be 10 even if you're using RDS based on MySQL 5.7. Review your db parameter group.

---

Related Topics