Skip to content

Insight and analysis of technology and business strategy

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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner