MySQL Can't Use Index With Uncorrelated IN Subquery
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.