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.
Share this
Previous story
← Oracle Gotcha: Months_between 31-days-per-month assumption
You May Also Like
These Related Stories
MySQL crashes on DDL statement: a lesson on purge threads
MySQL crashes on DDL statement: a lesson on purge threads
Dec 5, 2017
3
min read
Database sharding and the end of RAID?
Database sharding and the end of RAID?
Jun 7, 2007
1
min read
MSDB – Cleanup is necessary
![](https://www.pythian.com/hubfs/Imported_Blog_Media/medium_5156498714.jpg)
MSDB – Cleanup is necessary
Oct 22, 2013
2
min read
No Comments Yet
Let us know what you think