MySQL Can't Use Index With Uncorrelated IN Subquery

by Pythian Marketing on Jan 9, 2007 12:00:00 AM

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.

MySQL Can't Use Index With Uncorrelated IN Subquery

No Comments Yet

Subscribe by email

Share this

MySQL Can't Use Index With Uncorrelated IN Subquery

Share this

No Comments Yet

Subscribe by email