On Mon, Jun 09, 2003 at 12:46:32PM -0500, Christopher Knight wrote: > Im have a problem / misunderstanding with the 4.1 release. Im having issues > with indexes related to 'IN' vs '=' in subselects. > assume the subselect returns 1 value > > select SQL_NO_CACHE user_id from table_a where user_id in (select > SQL_NO_CACHE user_id from table_b where ... blah) > 9.5 seconds... > > select SQL_NO_CACHE user_id from table_a where user_id = (select > SQL_NO_CACHE user_id from table_b where ... blah) > 0.0 seconds > > explain select SQL_NO_CACHE user_id from table_a where user_id in (select > SQL_NO_CACHE user_id from table_b where ... blah) > ... not using index... > > explain select SQL_NO_CACHE user_id from table_a where user_id = (select > SQL_NO_CACHE user_id from table_b where ... blah) > .. using index on user_id > > and the subselect take 0.0 seconds to run as well.. > > > Does just using the IN operator prohibit table_a from using the user_id > index?
It's a known bug that I ran into a while back too. The IN condition wasn't optimized in the 4.1.0 release. I think it's already fixed in the BK tree but havne't checked yet. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 195,467,760 queries (350/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
