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]
