Although parentUserId is indexed, the fact that you're using an IN list turns the query into a range. IN lists are fast but at certain levels such as yours it is not. The reasons are listed below:
The query parser must allocate memory for every string in the list and convert it into an int. So your using prob a few megs of memory to parse the IN list. Next the query optimizer notices that the IN list is very large, thus for efficiency it must perform passes or a range across the binary tree. If this range covers more then 30% of your table, the optimizer will determine that a full table scan is faster and will not use the index. To improve throughput: Put the 60K ints into a temporary table. Next join against this temporary table. This join forces the optimizer to translate the range into an eq_ref-one of the fastest type joins. DVP ---- Dathan Vance Pattishall http://www.friendster.com > -----Original Message----- > From: Mitul Bhammar [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 17, 2004 9:22 PM > To: [EMAIL PROTECTED] > Subject: using IN() > > I've a bunch of IDs fetched(around 60,000) from a DB. > I'm using these IDs to fetch data from another DB > having a related fields in its tables. I'm using IN > clause for it. i.e. for e.g. SELECT * FROM site_users > WHERE parentUserId IN (1,2,3,4....) > > Again here parentUserId is Indexed. > > The query is running fine for now. I wanted to know > how MySQL interprets and executes this query and can > it have problems in future if number exceeds 60,000?? > > > > __________________________________ > Do you Yahoo!? > The all-new My Yahoo! - Get yours free! > http://my.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]
