I have an application at the minutes that loops through a multidimensional array and builds a query from it, currently the query looks like this,

SELECT id FROM TABLE WHERE col_1 IN ("1,2,3") AND col_2 IN ("2,4,6")
OR WHERE col_1 IN ("5,7,9") AND col_2 IN ("10,11,12");

Now this query could have 100 and maybe thousands of WHERE/AND/OR clauses, is faster to run query with x number of WHERE clauses, or would it be more prudent to run a query for each WHERE permutation?

  • are the in lists mean to be quoted? is the source multidimentional array also based on a SQL query? If so, maybe that can be integrated into single query rather than a distributed database that is 1/2 implemented in your application. – danblack Nov 21 '18 at 0:32
  • Of course one query is faster. You can additionally try to convert your query to UNION ALL, but I think it will not speed up. currently the query looks like this Syntax error. – Akina Nov 21 '18 at 10:54

One query is faster than two. Try this:

SELECT id FROM TABLE WHERE (col_1,col_2)
   IN (("1,2,3","2,4,6"),("5,7,9","10,11,12"));

Unless you were to store your array in a temporary table first.

Check if mysql has limits on the number of elements in an in-list.

  • I've seen 70K items in an IN list. I have not heard of a direct limit; there are likely to be RAM, etc, limits. – Rick James Nov 20 '18 at 23:05
  • and max_packet_length :-) – danblack Nov 21 '18 at 0:32
  • The terms in an IN list are likely to only range between 10-1k however theoretially I could have 1k combinations of WHERE OR AND Clauses. – Udders Nov 21 '18 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.