MySQL Stored Procedure, taking ages to run, only 5 records in the DB!!!

[vjmehra]

Apologies for this post as I'm sure its a very stupid question, but is there anyway I can speed this up?

It takes nearly 29 seconds to run and there are only 5 records in the main tblissue, within the DB, which just seems odd to me. I appreciate there are a number of left joins, but I'm still suprised as to how slow it actually is...

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_filter_issues_description`(
IN var_description varchar (20)
)
BEGIN

drop temporary table if exists tmptblfilter;

create temporary table tmptblfilter(
issue_id int
);

insert into tmptblfilter(issue_id)

select issue_id
from tblissue
where description like var_description;

drop temporary table if exists tmptblcurrentbenchmark;

CREATE temporary TABLE tmptblcurrentbenchmark (
issue_id int,
Description varchar(45)
);

insert into tmptblcurrentbenchmark(
issue_id,
Description)

select
a.issue_id,
a.Description
from tblissue a
inner join tblbenchmark b on a.issue_id = b.issue_id
where a.issue_id in (select issue_id from tblbenchmark);

drop temporary table if exists tmptblissuebenchmark;

CREATE temporary TABLE tmptblissuebenchmark (
issue_id int,
Description varchar(45) DEFAULT NULL
);

insert into tmptblissuebenchmark(
issue_id,
Description)

select
a.issue_id,
a.Description
from tblissue a
inner join tblbenchmark b on a.issue_id = b.issue_id
where a.issue_id in (select issue_id from tblbenchmark);

drop temporary table if exists tmptblprice;

create temporary table tmptblprice(
price_id int,
issue_id int,
bid_price decimal(6,3),
ask_price decimal(6,3),
bid_asw decimal(6,3),
ask_asw decimal(6,3),
bid_sprd decimal(6,3),
ask_sprd decimal(6,3),
bid_ytc decimal(6,3),
ask_ytc decimal(6,3),
bid_ytm decimal(6,3),
ask_ytm decimal(6,3),
price_date date,
price_time time,
exchange_id int
);

insert into tmptblprice(
price_id, issue_id, bid_price, ask_price,bid_asw,ask_asw,bid_sprd,ask_sprd,bid_ytc,ask_ytc,bid_ytm,ask_ytm,price_date,price_time,ex change_id
)
select price_id, issue_id, bid_price, ask_price,bid_asw,ask_asw,bid_sprd,ask_sprd,bid_ytc,ask_ytc,bid_ytm,ask_ytm,price_date,price_time,ex change_id
from tblprice
where price_id in (select max(price_id)
from tblprice
where issue_id in (select issue_id from tmptblfilter)
group by issue_id)
order by price_date desc, price_time desc
;

SELECT a.issue_id,a.sub_sector_id,a.cusip,a.bloomberg_number,a.isin,a.Issuer_id,a.guarantor_id,a.market_id, a.amount_issued,a.amount_outstanding,
a.min_size,a.increment,a.par_amount,a.maturity_date,a.first_call_date,a.coupon,a.coupon_type_id,a.co upon_frequency_id,a.day_count_id,
a.announcement_date,a.interest_accrual_date,a.first_settle_date,a.first_coupon_date,a.issue_price,a. issue_benchmark_id,a.current_benchmark_id,
a.issue_swap_spread,a.issue_benchmark_spread,a.coupon_step,a.currency_id,a.description,b.sub_sector_ name,b.sector_id,c.sector_name,
d.institution_name as 'issuer_name',d.bond_ticker as 'issuer_ticker',e.institution_name as 'guarantor_name',e.bond_ticker as 'guarantor_ticker',
f.market_name, g.coupon_type_name,h.coupon_frequency_name,h.coupon_frequency_code, i.day_count_name,k.description as 'issue_benchmark_description',
m.description as 'current_benchmark_description',n.currency_name,n.currency_ticker,o.country_name as 'issuer_country_name',
o.country_code as 'issuer_country_code',p.country_name as 'guarantor_country_name',p.country_code as 'guarantor_country_code',
q.exchange_issue_id,r.exchange_id,r.exchange_name,r.exchange_code,a.capital_id, s.capital_name, a.collateral_type_id, t.collateral_type_name,
u.price_id, u.bid_price, u.ask_price, u.bid_asw, u.ask_asw, u.bid_sprd, u.ask_sprd, u.bid_ytc, u.ask_ytc, u.bid_ytm, u.ask_ytm, u.price_date,
u.price_time, u.exchange_id as 'source_id', v.exchange_code as 'source_code'
FROM tblissue a
inner join tblsub_sector b on a.sub_sector_id = b.sub_sector_id
inner join tblsector c on b.sector_id = c.sector_id
inner join tblinstitution d on a.issuer_id = d.institution_id
inner join tblinstitution e on a.guarantor_id = e.institution_id
left join tblmarket f on a.market_id = f.market_id
inner join tblcoupon_type g on a.coupon_type_id = g.coupon_type_id
inner join tblcoupon_frequency h on a.coupon_frequency_id = h.coupon_frequency_id
inner join tblday_count i on a.day_count_id = i.day_count_id
left join tmptblissuebenchmark k on a.issue_benchmark_id = k.issue_id
left join tmptblcurrentbenchmark m on a.current_benchmark_id = m.issue_id
inner join tblcurrency n on a.currency_id = n.currency_id
inner join tblcountry o on d.country_id = o.country_id
inner join tblcountry p on e.country_id = p.country_id
left join tblexchange_issue q on a.issue_id = q.issue_id
left join tblexchange r on q.exchange_id = r.exchange_id
left join tblcapital s on a.capital_id = s.capital_id
left join tblcollateral_type t on a.collateral_type_id = t.collateral_type_id
left join tmptblprice u on a.issue_id = u.issue_id
left join tblexchange v on u.exchange_id = v.exchange_id
where a.issue_id in (select issue_id from tmptblfilter)
;

drop temporary table if exists tmptblprice;
drop temporary table if exists tmptblcurrentbenchmark;
drop temporary table if exists tmptblissuebenchmark;
drop temporary table if exists tmptblfilter;

END

[vjmehra]

Anyone? Am I just doomed with the slow speed due to the number of joins?

[smendoza]

I'd load all my temp tables with the data then the last query I'd do a explain on the query. That will tell you how the query is being executed, from there you can go about to optimize it from there.

EXPLAIN SELECT a.issue_id,a.sub_sector_id,a.cusip,a.bloomberg_number,a.isin,a.Issuer_id,a.guarantor_id,a.market_id, a.amount_issued,a.amount_outstanding,
a.min_size,a.increment,a.par_amount,a.maturity_date,a.first_call_date,a.coupon,a.coupon_type_id,a.co upon_frequency_id,a.day_count_id,
a.announcement_date,a.interest_accrual_date,a.first_settle_date,a.first_coupon_date,a.issue_price,a. issue_benchmark_id,a.current_benchmark_id,
a.issue_swap_spread,a.issue_benchmark_spread,a.coupon_step,a.currency_id,a.description,b.sub_sector_ name,b.sector_id,c.sector_name, 
d.institution_name as 'issuer_name',d.bond_ticker as 'issuer_ticker',e.institution_name as 'guarantor_name',e.bond_ticker as 'guarantor_ticker', 
f.market_name, g.coupon_type_name,h.coupon_frequency_name,h.coupon_frequency_code, i.day_count_name,k.description as 'issue_benchmark_description',
m.description as 'current_benchmark_description',n.currency_name,n.currency_ticker,o.country_name as 'issuer_country_name',
o.country_code as 'issuer_country_code',p.country_name as 'guarantor_country_name',p.country_code as 'guarantor_country_code',
q.exchange_issue_id,r.exchange_id,r.exchange_name,r.exchange_code,a.capital_id, s.capital_name, a.collateral_type_id, t.collateral_type_name,
u.price_id, u.bid_price, u.ask_price, u.bid_asw, u.ask_asw, u.bid_sprd, u.ask_sprd, u.bid_ytc, u.ask_ytc, u.bid_ytm, u.ask_ytm, u.price_date, 
u.price_time, u.exchange_id as 'source_id', v.exchange_code as 'source_code'
FROM tblissue a
inner join tblsub_sector b on a.sub_sector_id = b.sub_sector_id
inner join tblsector c on b.sector_id = c.sector_id
inner join tblinstitution d on a.issuer_id = d.institution_id
inner join tblinstitution e on a.guarantor_id = e.institution_id
left join tblmarket f on a.market_id = f.market_id
inner join tblcoupon_type g on a.coupon_type_id = g.coupon_type_id
inner join tblcoupon_frequency h on a.coupon_frequency_id = h.coupon_frequency_id
inner join tblday_count i on a.day_count_id = i.day_count_id
left join tmptblissuebenchmark k on a.issue_benchmark_id = k.issue_id
left join tmptblcurrentbenchmark m on a.current_benchmark_id = m.issue_id
inner join tblcurrency n on a.currency_id = n.currency_id
inner join tblcountry o on d.country_id = o.country_id
inner join tblcountry p on e.country_id = p.country_id
left join tblexchange_issue q on a.issue_id = q.issue_id
left join tblexchange r on q.exchange_id = r.exchange_id
left join tblcapital s on a.capital_id = s.capital_id
left join tblcollateral_type t on a.collateral_type_id = t.collateral_type_id
left join tmptblprice u on a.issue_id = u.issue_id
left join tblexchange v on u.exchange_id = v.exchange_id
where a.issue_id in (select issue_id from tmptblfilter)

[vjmehra]

Ok cool, I'm not familiar with explain, so I'll educate myself on that, looks like it could be quite useful :-)

That aside, would that explain why the performance in MySQL Query Analizer is fine, yet when the query is executed via a php page it is incredibly slow?

[smendoza]

So when you run the query in a mysql IDE the procedure runs quickly? But when you run the procedure through PHP is runs slow?

[vjmehra]

Yep, which confuses me!

[smendoza]

hm I'm not a php guy but that is odd. I would thought it would have ran slow in mysql IDE too not just php.