I attached the blog post I made on the subject a while back. (my blog is offline)
If you KNOW ahead of time that your subquery involves only a few columns, then just rewriting the query to become
SELECT * FROM FOO WHERE ID IN (?, ?, ? ... )
is MUCH faster.
So here's a constructive suggestion for fixing this.
Why not add a new feature called SQL_INLINE_SUBQUERY which tells MySQL that it should first run the dependent subquery, get back the results, then replace them inline.
SELECT SQL_INLINE_SUBQUERY * FROM FOO WHERE ID IN ( SELECT ID FROM BAR)
... and if BAR only had say ... 100 rows... it would be inlined instead of resorting to a full table scan.
Either that or (god forbid) fix this problem by having a smarter optimizer which can use a join.
Kevin
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.
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
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]
