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.
--
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
