MySQL Can’t Use Index With Uncorrelated IN Subquery
Jan 9, 2007 / By Alex Gorbachev
Today is the first time I had to look at MySQL performance. Tiny database as web application back-end was having significant performance issues with spikes of CPU workload. After identifying problematic queries, I found a pile of statements using IN subqueries. Typical example is:
SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t2 WHERE c2=100);
Checked explain plan – t1 is accessed via full table scan.
Note that table t1 had an index on column c1 and it was selective and statistics collected. Rewrote statement as
SELECT t1.* FROM t1, t2 WHERE t1.c1=t2.c1 AND t2.c2=100);
Query flies. Checked execution plan – index on t1 column is used.
Quick research located the bug #9021which might get fixed in 5.1.
I asked around and our MySQL experts assured me that this is one of the minor and not so disturbing issues, in fact. I can’t imagine what those disturbing issue are. I guess Oracle XE does have some advantages over MySQL for small installations.