Great explanison. I guest this is the point. after each time the table crashed during such process, it always followed a error message saying something about the memory violation. Thanks a lot. your idea also open my mind and deeper my understanding about mysql.
Monet --- Dathan Vance Pattishall <[EMAIL PROTECTED]> wrote: > 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] > > _______________________________ 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]
