Hi all, I'm developing financial analysis app in MySQL. Since I want it to be portable and purely client-server I'm coding (nearly) all calculations in SQL, and came across weird optimization issue. The following query calculates one of my ratios using financial data from TQRPE table (some 5K records), and RPDTE tables (contain date codes, 2-3 records):
select r1.rpco,min(r2.coeff)*(sum(t1.ams)-sum(t4.ams))/((sum(t2.ams)+sum(t3.ams))/2 ) from rpdte_full r1,rpdte_ann r2,tqrpe t1,tqrpe t2,tqrpe t3,tqrpe t4 where r1.rpco=r2.rpco and r1.rpco=t1.rpco and t1.rpco=t2.rpco and r1.prev_rpco=t3.rpco and t1.rpco=t4.rpco and t1.prcode like "I%" and (t2.prcode like "E%" or t2.prcode like "N%") and (t3.prcode like "E%" or t3.prcode like "N%") and t4.prcode like "XF%" group by r1.rpco Explain produces following results: table,type,possible_keys,key,key_len,ref,rows,Extra t4,range,PRIMARY,prcode,prcode,5,NULL,28,where used; Using temporary t1,ref,PRIMARY,prcode,PRIMARY,4,t4.rpco,6,where used t2,ref,PRIMARY,prcode,PRIMARY,4,t1.rpco,6,where used r1,eq_ref,PRIMARY,PRIMARY,4,t1.rpco,1, t3,ref,PRIMARY,prcode,PRIMARY,4,r1.prev_rpco,6,where used r2,eq_ref,PRIMARY,PRIMARY,4,r1.rpco,1, Weird thing is that t1...t3 tables are scanned using primary key while for t4 MySQL uses non-unique PRCODE index. As a result the query takes some 30sec to complete (which is unacceptable). Similar queries with just 3 tables (t1...t3) are used to calculate simpler ratios and complete in a wink. Any help appreciated! Table structures below Peter CREATE TABLE `tqrpe` ( `rpco` varchar(4) NOT NULL default '', `prcode` varchar(5) NOT NULL default '', `ams` double default NULL, PRIMARY KEY (`rpco`,`prcode`), KEY `prcode` (`prcode`) ) TYPE=MyISAM; CREATE TABLE `rpdte_ann` ( `rpco` varchar(4) NOT NULL default '', `coeff` double default NULL, PRIMARY KEY (`rpco`) ) TYPE=MyISAM; CREATE TABLE `rpdte_full` ( `rpco` varchar(4) NOT NULL default '', `rpdte` date default NULL, `prev_rpco` varchar(4) default NULL, PRIMARY KEY (`rpco`) ) TYPE=MyISAM; --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
