Re: subqueries *not* using indexes for IN clause
We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. greg Kevin A. Burton wrote: http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries: mysql EXPLAIN SELECT * FROM FEED, ARTICLE WHERE ARTICLE.ID = 1628011 AND FEED.ID = ARTICLE.ID *** 1. row *** id: 1 select_type: SIMPLE table: FEED type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec) Which is *great*. The join is using both of the PRIMARY indexes on the columns and only references one row. Can't get any better than that! Now lets rewrite the SELECT to use a subquery: mysql EXPLAIN SELECT * FROM FEED WHERE ID IN (SELECT ID FROM ARTICLE WHERE ID = 1628011) *** 1. row *** id: 1 select_type: PRIMARY table: FEED type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2316698 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec) And here's where the fun begins. The FEED table won't use *ANY* index! It really can't get ANY worse than that. So either this is a bug in both 4.1.10 and 4.1.7 or the optimizer is just plain broken. Note that using FORCE INDEX doesn't work at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subqueries *not* using indexes for IN clause
Greg Whalin wrote: We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. Yup... couldn't agree more! MySQL subqueries in 4.1 are at best useless and at worst Evil.. plain Evil ! ;) But nice try guys! This seems like it REALLY deserves a bug fix! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
