How can I improve the performance of this MySQL Query in Perl, the same Query directly executed in MySQL Workbench is 1600 times faster -

my mysql query in perl takes longer same query in mysql workbench. trying improve performance of perl query same workbench query.
running on microsoft windows 10 pro 64-bit, activeperl 5.24.0 build 2400 64-bit, using dbi v1.636, dbd-mysql 4.033 , mysql v5.7 64-bit.
there not resource constraint can find. innodb buffer pool – 40% utilized. table open cache efficiency of 99%. 64gb of ram, 16 processors running @ 3.4ghz. of queries run through environment acceptably fast. there 1 class of queries runs in perl, reasonably in workbench. here example of query.

select t1.csi_id, t2.signal_date, t2.nextgain                        equity t1         inner join               (equity_signal t2, market_boundary t3)  on  (t2.equity_csi_id = t1.csi_id ,  yearweek(t3.signal_date)= yearweek(t2.signal_date))                     yearweek(t2.signal_date) = 201643            , t2.currency_idcurrency = 'usd'         , t1.useequity = 1         , t1.nodata = 0         , t2.spike = 0         , t1.exchange in ('nyse','nasdaq')         , t2.liquidity>t3.value          , t3.currency='usd'         , t3.market='us'         , t3.type='lq'         , t3.threshold=500         , t2.stddev < 1         , (t2.calcbool & 63) = 63     order (t2.buy)  desc limit 50 

here explain diagram query, when executes. query in mysql workbench took 4.047 seconds.

t1 in query has 29000 rows, 14 columns , 3.5mb. t2 in query has 8500000 rows, 34 columns , 1.7gb 1.2gb index. t3 in query has 54000 rows, 7 columns , 3.5 mb

explain statement results workbench

the explain statement results when executed through perl follows, same tabular result received workbench:
1 simple t3 ref primary primary 38 const 27354 0.10 using where; using temporary; using filesort
1 simple t1 primary 29304 0.20 using where; using join buffer (block nested loop)
1 simple t2 ref primary,liquidity,buy,stddev primary 4 investing2.t1.csi_id 92 0.56 using

during execution of query, there no concurrent or competing processes of creation accessing or using tables in question. when run same query in perl, have mysql using 13% of available cpu (across 16 cpu’s) on 100 minutes.

case 1, portfolio = 11 yearweek = 201644
portdatah start sat nov 5 14:47:00 2016 end sat nov 5 16:33:23 2016
portfolio = 11 yearweek = 201644
case 1, portfolio = 11 yearweek = 201643
portdatah start sat nov 5 16:33:23 2016 end sat nov 5 18:19:47 2016

so running times of 106 minutes , 23 seconds first time, , 106 minutes , 24 seconds second run. on entirely different platform, ( mysql 5.5, 32 gb ram, perl 5.16.3) same code works comparable performance workbench observed results.
here think relevant code snippets.

#!d:\perl64\bin use strict; use warnings; use dbi; use list::util qw(first); use threads; use date::simple (':all'); use switch::plain; @portfolio; $portfolio[11]="select t1.csi_id, t2.signal_date, t2.nextgain                        equity t1         inner join               (equity_signal t2, market_boundary t3)  on (t2.equity_csi_id = t1.csi_id  , yearweek(t3.signal_date)=yearweek(t2.signal_date))                  yearweek(t2.signal_date) = 201643            , t2.currency_idcurrency = 'usd'         , t1.useequity = 1         , t1.nodata = 0         , t2.spike = 0         , t1.exchange in ('nyse','nasdaq')         , t2.liquidity>t3.value          , t3.currency='usd'         , t3.market='us'         , t3.type='lq'         , t3.threshold=500         , t2.stddev < 1         , (t2.calcbool & 63) = 63     order (t2.buy)  desc limit 50"; $thread11 = threads->create(\&portfolioa,11,$portfolio[11]); $return = $thread11->join();  sub portfolioa { $portid = shift;     $portsql = shift;     $portdatah = $dbh->prepare($portsql)         or die "couldn't prepare statement: " . $dbh->errstr; $starttime=localtime();     $portdatah->execute($yearweek)         or die "couldn't execute statement: " . $dbh->errstr;     $endtime=localtime();     print "portdatah start $starttime  end $endtime \n";     

i have looked errors in logs, have tried tune mysql server. innodb buffer pool 12 gb, join buffer size 4gb, sort buffer size 4gb, there 8 innodb page cleaners, query cache size 2gb. added index on signal_date equity_signal largest table in database. considering trying install previous versions of perl , mysql see if resolves things. don’t see missing.

here results single query using trace level 4.

    dbi::st=hash(0x3081000) trace level set 0x0/4 (dbi @ 0x0/0) in dbi 1.636-ithread (pid 10776) -> execute dbd::mysql::st (dbi::st=hash(0x3081318)~0x3081000 '201644') thr#26278d8 called: dbd_bind_ph -> dbd_st_execute 03081360 >- dbd_st_free_result_sets <- dbd_st_free_result_sets rc -1 <- dbd_st_free_result_sets mysql_st_internal_execute mysql_version_id 50172 >parse_params statement select t1.csi_id, t2.signal_date, t2.nextgain                                                                equity t1                             inner join                                   (equity_signal t2, market_boundary t3) on (t2.equity_csi_id = t1.csi_id , yearweek(t3.signal_date)=yearweek(t2.signal_date))                                                                yearweek(t2.signal_date) = ?                                 , t2.currency_idcurrency = 'usd'                             , t1.useequity = 1                             , t1.nodata = 0                             , t2.spike = 0                             , t1.exchange in ('nyse','nasdaq')                             , t2.liquidity>t3.value                              , t3.currency='usd'                             , t3.market='us'                             , t3.type='lq'                             , t3.threshold=500                             , t2.stddev < 1                             , (t2.calcbool & 63) = 63                         order t2.buy desc                                 limit 50 binding parameters: select t1.csi_id, t2.signal_date, t2.nextgain                                                                equity t1                             inner join                                   (equity_signal t2, market_boundary t3) on (t2.equity_csi_id = t1.csi_id , yearweek(t3.signal_date)=yearweek(t2.signal_date))                                                                yearweek(t2.signal_date) = '201644'                              , t2.currency_idcurrency = 'usd'                             , t1.useequity = 1                             , t1.nodata = 0                             , t2.spike = 0                             , t1.exchange in ('nyse','nasdaq')                             , t2.liquidity>t3.value                              , t3.currency='usd'                             , t3.market='us'                             , t3.type='lq'                             , t3.threshold=500                             , t2.stddev < 1                             , (t2.calcbool & 63) = 63                         order t2.buy desc                                 limit 50 dbi::st=hash(0x2f71530) trace level set 0x0/4 (dbi @ 0x0/0) in dbi 1.636-ithread (pid 11064) -> execute dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530 '201644') thr#25178d8 called: dbd_bind_ph -> dbd_st_execute 02f71890 >- dbd_st_free_result_sets <- dbd_st_free_result_sets rc -1 <- dbd_st_free_result_sets mysql_st_internal_execute mysql_version_id 50172 >parse_params statement select t1.csi_id, t2.signal_date, t2.nextgain                                                                equity t1                             inner join                                   (equity_signal t2, market_boundary t3) on (t2.equity_csi_id = t1.csi_id , yearweek(t3.signal_date)=yearweek(t2.signal_date))                                                                yearweek(t2.signal_date) = ?                                 , t2.currency_idcurrency = 'usd'                             , t1.useequity = 1                             , t1.nodata = 0                             , t2.spike = 0                             , t1.exchange in ('nyse','nasdaq')                             , t2.liquidity>t3.value                              , t3.currency='usd'                             , t3.market='us'                             , t3.type='lq'                             , t3.threshold=500                             , t2.stddev < 1                             , (t2.calcbool & 63) = 63                         order t2.buy desc                                 limit 50 binding parameters: select t1.csi_id, t2.signal_date, t2.nextgain                                                                equity t1                             inner join                                   (equity_signal t2, market_boundary t3) on (t2.equity_csi_id = t1.csi_id , yearweek(t3.signal_date)=yearweek(t2.signal_date))                                                                yearweek(t2.signal_date) = '201644'                              , t2.currency_idcurrency = 'usd'                             , t1.useequity = 1                             , t1.nodata = 0                             , t2.spike = 0                             , t1.exchange in ('nyse','nasdaq')                             , t2.liquidity>t3.value                              , t3.currency='usd'                             , t3.market='us'                             , t3.type='lq'                             , t3.threshold=500                             , t2.stddev < 1                             , (t2.calcbool & 63) = 63                         order t2.buy desc                                 limit 50  <- dbd_st_execute returning imp_sth->row_num 19     <- execute= ( 19 ) [1 items] @ c:/workspace/diy investing/csi/i2portfolio1debug.pl line 873 via  @ c:/workspace/diy investing/csi/i2portfolio1debug.pl line 700     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=19     dbd_st_fetch 02f71890, currow= 1     <- dbd_st_fetch, 3 cols     <- fetchrow_array= ( '9825' '2016-11-04' '0' ) [3 items] row1 @ c:/workspace/diy investing/csi/i2portfolio1debug.pl line 878 via  @ c:/workspace/diy investing/csi/i2portfolio1debug.pl line 700     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 2     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 3     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 4     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 5     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 6     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 7     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 8     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 9     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 10     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 11     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 12     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 13     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 14     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 15     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 16     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 17     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 18     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 19     <- dbd_st_fetch, 3 cols     -> fetchrow_array dbd::mysql::st (dbi::st=hash(0x2f71848)~0x2f71530) thr#25178d8     -> dbd_st_fetch         dbd_st_fetch 02f71890, chopblanks 0     dbd_st_fetch result set details     imp_sth->result=03a71110     mysql_num_fields=3     mysql_num_rows=19     mysql_affected_rows=1     dbd_st_fetch 02f71890, currow= 20     dbd_st_fetch, no more rows fetch --> dbd_st_finish     >- dbd_st_free_result_sets     <- dbd_st_free_result_sets rc -1     <- dbd_st_free_result_sets 

i'll try re-work queries avoid yearweek (i using yearweek, because data weekly, friday last day , thursday, , want compare weeks between markets - not in query example), should able use interval well. also, i'm thinking of trying whole thing in python or ruby, opposed installing back-level products.

here create table entries each table:

t1 - equity

create table `equity` (   `csi_id` int(11) not null,   `symbol` varchar(45) not null,   `equityname` varchar(45) not null,   `exchange` varchar(45) not null,   `currency_idcurrency` varchar(6) not null,   `priceform` int(11) default null,   `startdate` date default null,   `lastupdate` date default null,   `industry_idsector` varchar(10) not null,   `industry_idindustry` varchar(10) not null,   `nodata` tinyint(1) not null default '0',   `useequity` tinyint(1) not null default '1',   `category` varchar(10) default null,   `outshares` float default null,   primary key (`csi_id`) ) engine=innodb default charset=utf8; 

t2 equity_signal (i'm not showing unused columns in table)

create table `equity_signal` (   `equity_csi_id` int(11) not null,   `signal_date` date not null,   `currency_idcurrency` varchar(6) not null,   `signal_sequence` int(11) not null,   `adjusted_close` float not null,   `liquidity` float not null,   `gain` float not null,   `nextgain` float default null,   `stddev` double not null,   `spike` tinyint(1) not null,   `buy` float not null,   `calcbool` int(11) not null,   primary key (`equity_csi_id`,`signal_date`,`currency_idcurrency`),   key `liquid` (`currency_idcurrency`,`signal_date`,`liquidity`),   key `buy` (`currency_idcurrency`,`signal_date`,`buy`),   key `std` (`currency_idcurrency`,`signal_date`,`stddev`),   key `signal_date` (`signal_date`) ) engine=innodb default charset=utf8; 

and t3 market_boundary

create table `market_boundary` (   `market` varchar(12) not null,   `signal_date` date not null,   `currency` varchar(6) not null,   `type` varchar(6) not null,   `threshold` int(11) not null,   `percent` float not null,   `value` float not null,   primary key (`market`,`signal_date`,`currency`,`type`,`threshold`) ) engine=innodb default charset=utf8; 

change yearweek tests

where signal_date >= '2016-10-22'   , signal_date  < '2016-10-22' + interval 1 week 

(or whatever correct date is.)

and separately check test t3.signal_date , t2.signal_date way.

then build suitable composite index on each table -- ending signal_date.

after seeing show create table, may have more advice.

back question... don't have answer of why 1 client run soooo faster other. (my caching comment not sufficient explain 1600x.) but, fixing index should both clients, perhaps more 1600x.