On 28-Jan-2004 Batara Kesuma wrote: > Hi, > > I do my query from Perl DBI, it has a very big IN ( $value ). > The query looks like: > SELECT id FROM user > WHERE sex='m' > AND hobby_id='3' > AND id IN ( $value ); > > $value looks like: > 102, 233, 445, 543 ... 3443, 332 very long list. > > It might be about 10,000 id in $value. What is the maximum value I > can > pass in a query? Is this the right way to do it? (I mean, using a > very > big value inside IN) >
You'll be limited to the 'max_allowed_packet' (1 megabyte) query string length. Assuming 6 digits in an id, you get: 10,000 * (6 + 2) [comma+space] = 80,000 bytes (+ rest of query) So you will be okay with that particular string, but that is going to put a nasty hit on the server unless you index the table with something like: KEY idx_hsi (hobby_id, sex, id). At a higher level you might rethink how/why you're doing it that way. This is a subselect done in two steps. Look if you could combine/modify your original query (that produced $value) and this query to a single JOIN query. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
