List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 19 2010 8:06pm
Subject:Re: Subquery performance slow to non-existent...
View as plain text  
In the last episode (Jan 19), Nick Torenvliet said:
> Hey all...
> 
> I am having a problem with sub-queries that I cannot trouble shoot.
> 
> I run query a:
> select symbol from names where market like 'NYMEX' and name like 'natural
> gas {%';
> 
> and get  168 names that I manually insert into query b:
>  select * from endOfDayData where endOfDayData.market like 'NYMEX' and
> endOfDayData.symbol IN
>
> ("NGF09","NGF10","NGF11","NGF12","NGF13","NGF14","NGF15","NGF16","NGF17","NGF18","NGF19","NGF20",
>
> "NGF21","NGF22","NGG09","NGG10","NGG11","NGG12","NGG13","NGG14","NGG15","NGG16","NGG17","NGG18","NGG19","NGG20","NGG21","NGG22","NGH09","NGH10","NGH11","NGH12","NGH13","NGH14","NGH15","NGH16",
>
> "NGH17","NGH18","NGH19","NGH20","NGH21","NGH22","NGJ09","NGJ10","NGJ11","NGJ12","NGJ13","NGJ14","NGJ15","NGJ16","NGJ17","NGJ18","NGJ19","NGJ20","NGJ21","NGJ22","NGK09","NGK10","NGK11","NGK12",
>
> "NGK13","NGK14","NGK15","NGK16","NGK17","NGK18","NGK19","NGK20","NGK21","NGK22","NGM09","NGM10","NGM11","NGM12","NGM13","NGM14","NGM15","NGM16","NGM17","NGM18","NGM19","NGM20","NGM21","NGM22",
>
> "NGN09","NGN10","NGN11","NGN12","NGN13","NGN14","NGN15","NGN16","NGN17","NGN18","NGN19","NGN20","NGN21","NGN22","NGQ09","NGQ10","NGQ11","NGQ12","NGQ13","NGQ14","NGQ15","NGQ16","NGQ17","NGQ18",
>
> "NGQ19","NGQ20","NGQ21","NGQ22","NGU09","NGU10","NGU11","NGU12","NGU13","NGU14","NGU15","NGU16","NGU17","NGU18","NGU19","NGU20","NGU21","NGU22","NGV09","NGV10","NGV11","NGV12","NGV13","NGV14",
>
> "NGV15","NGV16","NGV17","NGV18","NGV19","NGV20","NGV21","NGV22","NGX09","NGX10","NGX11","NGX12","NGX13","NGX14","NGX15","NGX16","NGX17","NGX18","NGX19","NGX20","NGX21","NGX22","NGZ09","NGZ10",
>
> "NGZ11","NGZ12","NGZ13","NGZ14","NGZ15","NGZ16","NGZ17","NGZ18","NGZ19","NGZ20","NGZ21","NGZ22");
> 
> Running query b gives me a result set as follows:
> 
> | 2010-01-15 | NYMEX  | NGZ22  |  8.9620 |  8.9680 |  8.9620 |  8.9680
> |      0 |
> +------------+--------+--------+---------+---------+---------+---------+--------+
> 86765 rows in set (4.46 sec)
> 
> I then because I want to generalize query b I continue by creating query c
> as follows:
> mysql> select * from endOfDayData where endOfDayData.market like 'NYMEX'
> and  endOfDayData.symbol IN (select names.symbol from names where
> names.market like  'NYMEX' and names.name like 'natural gas {%');
> 
> Query c seems to have good syntax as neither the command line mysql
> interface nor the gui spit it back but it literally takes forever to run;
> I've waited at least twenty minutes and not got anything back.  I'm running
> Ubuntu 9.10 on an intel core i7 with 4GB RAM and 12GB swap... the process
> monitor doesn't even flinch so I'm not thinking hardware here... why is the
> sub-query running so slow?

MySQL's subquery optimizer is pretty bad.  I bet if you explain that query,
mysql thinks the subquery is dependant.  That means that it will run the
subquery for each row of the outer query, even though it's obviously not
going to change from row to row.  Your best bet for now is to do what you're
currently doing with queries A and B.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Subquery performance slow to non-existent...Nick Torenvliet19 Jan
  • Re: Subquery performance slow to non-existent...Dan Nelson19 Jan
  • Re: Subquery performance slow to non-existent...Shawn Green19 Jan
  • Re: Subquery performance slow to non-existent...Peter Brawley19 Jan