Monday, October 29, 2007
« 8 Reasons to Join Semingo Now | Main | Announcing khjgf.com »
MySQL: Slow Query When Joining with a Union

The MySQL query optimizer has trouble with queries that contain a join to a union.
Lets take a look at the following example:

select u.f1, u.f2 from
(select t1.f1, t2.f2 from t1 join t2 on t1.f3 = t2.f3
union
select t3.f1, t2.f2, from t3 join t2 on t3.f3 = t2.f3) u
where u.f2 = ?val


Now, suppose we have an index on column f2 in t2. If you look at the explain plan, you will see that MySQL does not use it.
So, the solution is to move the where constraint into the parts of the union itself:
select u.f1, u.f2 from
(select t1.f1, t2.f2 from t1 join t2 on t1.f3 = t2.f3
where t2.f2 = ?val
union
select t3.f1, t2.f2, from t3 join t2 on t3.f3 = t2.f3
where t2.f2 = ?val) u


This time, the index on f2 will be used and the query should perform much better.

More on MySQL at pashabitz.com:
MySQL .Net Connector Bug

Want to join me as partner in a cool new startup?
Get in touch: pasha at cohai dot co

Bookmark and Share Tuesday, October 30, 2007 4:10:38 AM (Jerusalem Standard Time, UTC+02:00)  #    Comments [8]  
Sunday, November 18, 2007 3:49:23 PM (Jerusalem Standard Time, UTC+02:00)
It does make sense.

It's like in elementary school, they teach you that if you need to do (a + b) / c, and (a + b) is large while you know to do (a / c) and (a / b) by heart - then you should do it.

I do not think that you should put too much power in the hand of the query optimizers, especially if you're into smashing large numbers as you guy at Semingo appear to be, according to you're "we're recruiting" post/
Ken Egozi | mailAT NOSPAMkenegozi dot com
Monday, May 31, 2010 5:53:31 AM (Jerusalem Daylight Time, UTC+03:00)
Mysql actually is using a temporary table when you use a union. Do an explain plan on the query and you will see it....

Bad situation.
kalyson | gaiusgracchus33AT NOSPAMyahoo dot com
Saturday, April 16, 2011 6:16:08 PM (Jerusalem Daylight Time, UTC+03:00)
snmldj TYVM you've svoeld all my problems
Rileigh | dan dot chenAT NOSPAMusask dot ca
Saturday, April 16, 2011 9:10:13 PM (Jerusalem Daylight Time, UTC+03:00)
opQfAX <a href="http://xivmizpschua.com/">xivmizpschua</a>
igxrqowaks | egflvaAT NOSPAMsllhjm dot com
Saturday, April 23, 2011 7:50:15 PM (Jerusalem Daylight Time, UTC+03:00)
kPABUk <a href="http://owknhmznixad.com/">owknhmznixad</a>
zzjzhw | bnpcifAT NOSPAMsgvvll dot com
Saturday, April 23, 2011 7:50:16 PM (Jerusalem Daylight Time, UTC+03:00)
uDjcJl <a href="http://ilbutgnzpltu.com/">ilbutgnzpltu</a>
itevwekr | hlhrwrAT NOSPAMuevajx dot com
Sunday, April 24, 2011 11:33:41 PM (Jerusalem Daylight Time, UTC+03:00)
X8RW5T , [url=http://nhgagwpkbawe.com/]nhgagwpkbawe[/url], [link=http://isjfzmcinkjj.com/]isjfzmcinkjj[/link], http://drwmeevlvmyj.com/
fokokvavh | wpgtelAT NOSPAMpjqizx dot com
Monday, December 19, 2011 8:13:35 PM (Jerusalem Standard Time, UTC+02:00)
It's understandable that cash can make people disembarrass. But what to do when one doesn't have cash? The one way is to receive the loans or sba loan.
loans | donnaedwardsAT NOSPAMmail15 dot com
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):