List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 19 2010 9:22pm
Subject:Re: Subquery performance slow to non-existent...
View as plain text  
Nick,

>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;

IN(SELECT...) is slow in MySQL; for alternatives see "The unbearable slowness of IN()" at
http://www.artfulsoftware.com/infotree/queries.php. 

Try something like this?

select * 
from endOfDayData d
join names n using (symbol)
where d.market = 'NYMEX' and 'natural gas {' = substr( n.name,1,13);

PB

-----

Nick Torenvliet wrote:
> 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?
>
> Thanks for you help!!
>
> Nick
>
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.432 / Virus Database: 270.14.150/2632 - Release Date: 01/19/10 07:34:00
>
>   

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