Re: Very long "WHERE" list.

On Sun, 11 Jul 2004 20:07:41 +1000, Noons <wizofoz2k_at_yahoo.com.au.nospam> wrote:

>Andy Hassall apparently said,on my timestamp of 10/07/2004 10:40 PM:
>
>> A quick test shows that MySQL 4.0.18 can.
>>
>> #!/usr/bin/perl
>> my $monstroInlist = join ',', (1..20000);
>> print <<END_SQL;
>> select count(*)
>> from t
>> where id in ($monstroInlist);
>> END_SQL
>
>Mind creating a linear list with all the values as proposed by
>the OP?

 The above is already a linear list from 1 to 20000, although it's twice the size of the OP's example. I see the OP had every other element missing, so changed one line in the perl script to:

my $monstroInlist = join ',', grep { $_%2==1 } (1..20001);

 ... to just get odd numbers and to go up to 20001 rather than 20000.

andyh_at_server:~/tmp$ cat monstroInlist.sql select count(*)
from t
where id in (1,3,5,7,9,11,13,15,17,19,21,23,25,27, [ snip, goes up to 20001 ]

mysql> \. monstroInlist.sql
+----------+
| count(*) |
+----------+
| 10001 |
+----------+
1 row in set (2.38 sec)

> I think you will find that it will exceed the max
>SQL statement size.

 I don't see why - this statement's now half the size of the one I ran earlier.

> What if the list had the third element missing after every dozen?

 Then it'd be even smaller. The following's not quite right for that but is somewhere similar:

my $monstroInlist = join ',', grep { $_%2==1 && !($_%12==3)} (1..20000);

mysql> \. monstroInlist.sql
+----------+
| count(*) |
+----------+
| 8333 |
+----------+
1 row in set (2.90 sec)  

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space