List:General Discussion« Previous MessageNext Message »
From:Donny Simonton Date:February 17 2005 5:24am
Subject:RE: What is the max length of IN() function?
View as plain text  
Actually, I've done a test with this in the past, we could not find a limit.
But there is a magic number where the optimizer stops doing a good job of
optimizing the query and it starts to get really slow.

In our case we were using words, and phrases, so we would have something
like:

IN ('a', 'apple', 'apple car', 'car', 'c')  etc...

We found that once it hits about 200 or so entries the query went from 0.00
seconds to about 2-3 seconds.  Sometimes much more.

Donny

> -----Original Message-----
> From: Tom Crimmins [mailto:mysql2@stripped]
> Sent: Wednesday, February 16, 2005 9:07 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: RE: What is the max length of IN() function?
> 
> 
> > -----Original Message-----
> > From: Daevid Vincent
> > Sent: Wednesday, February 16, 2005 20:59
> > To: mysql@stripped
> > Subject: What is the max length of IN() function?
> >
> > I tried to find this function on the dev.mysql.com site, but good luck
> > finding "in"... ;-)
> >
> > Can someone tell me what the maximum length is for this function?
> >
> > SELECT * FROM foo WHERE bar IN(1,2,3,4,..... N);
> >
> > How many entries can there be in between 1 and N ? Hundreds?
> > Thousands?
> > Millions?
> 
> From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, "The
> number of values in the IN list is only limited by the max_allowed_packet
> value."
> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1


Thread
What is the max length of IN() function?Daevid Vincent17 Feb
RE: What is the max length of IN() function?Tom Crimmins17 Feb
  • RE: What is the max length of IN() function?Donny Simonton17 Feb
    • Re: What is the max length of IN() function?Keith Ivey17 Feb