IN is a fantastic operator, but there are some limitations especially memory wise.
Check out this algorithm Say you're using an Integer with an average of 8 digits, i.e. in the 100s millions, now you send an in list of say 20 of these 8 digits numbers. Since the data is passed to mysql as a string, the parser has to allocate memory for 160 bytes (20 * 8 bytes) + 19 bytes for each comma. 339 bytes BAH that's nothing right? Well, these are bytes allocated outside of a key buffer, thus if your key buffer is set to 1.9 GB on a 32 bit system, your application has many of these IN list passed to it, mysql will crash because it just hit the 2 GB limit. Does this explain your issue, no not necessarily but it's good to add and might explain some weird experiences. DVP ---- Dathan Vance Pattishall http://www.friendster.com > -----Original Message----- > From: Monet [mailto:[EMAIL PROTECTED] > Sent: Monday, September 13, 2004 10:22 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: IN operator > > Hi there, > I used very likely statement last week to update one > table. My IN value is around 20. I checked the manual > and there is nothing about any limitation on IN > values. > However, when i was running it, it worked very well > sometimes, while sometimes, the query crashed in the > middle and i have to REPAIR table. I've not figure out > the reason of the crash yet. but i think you should be > aware of it. > > Monet > > --- Oliver Hirschi <[EMAIL PROTECTED]> wrote: > > > Hi people > > > > Due to MySQL does not support "inner-selects", I > > generate a string (I > > programm java-client) with the values I used in an > > IN-operator for an > > update onto a mySQL database. > > > > The statement looks like this: > > UPDATE layer SET State=1 > > WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) > > > > Now, the question came up if there is a maximum of > > values or length in > > an IN operator which can used on mySQL? > > > > Does anybody know something about that? > > > > Thanks & Regards > > -- > > Oliver Hirschi > > http://www.FamilyHirschi.ch > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > _______________________________ > Do you Yahoo!? > Declare Yourself - Register online to vote today! > http://vote.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
