MySQL Can't Use Index With Uncorrelated IN Subquery

1 min read

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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

MySQL Can't Use Index With Uncorrelated IN Subquery

Share this

Share this

More resources

The hidden cost of too many indexes in MySQL

Multi-Column Correlation and Extended Stats in Oracle 11g

Exposing MyRocks internals via system variables: Part 1, Data Writing

Ready to unlock value from your data?