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