MySQL Forums
Forum List  »  MySQL Query Browser

Please help with making this query quicker
Posted by: Bodi Klamph
Date: January 05, 2012 03:42PM

Hey,

I've been trying to figure out why this query takes over 10s to run for a a few days now with no luck so I'm here looking for some assistance. The query returns 70 rows and the table itself only has 30,000 rows.


select
fo_historical_balances_by_account_total.JPM_Account AS JPM_Account,
count(fo_historical_balances_by_account_total.ID) AS Days,
cast(ifnull(margin_date.MaxDate, 1) as date) AS NotFound,
margin_date.MaxDate AS MaxDate,
margin_date.FirstDate AS FirstDate
from
(fo_historical_balances_by_account_total
left join margin_date ON ((margin_date.JPM_Account = fo_historical_balances_by_account_total.JPM_Account)))
where
fo_historical_balances_by_account_total.Statement_Date > ifnull(margin_date.MaxDate, FirstDate-1)
group by fo_historical_balances_by_account_total.JPM_Account,ifnull(margin_date.MaxDate, 1),margin_date.MaxDate,margin_date.FirstDate;


FYI, margin_date is a view that returns some IDs and Dates...it runs quickly by itself. There are indexes on all necessary fields.

And here is the EXPLAIN:


'1', 'PRIMARY', 'fo_historical_balances_by_account_total', 'ALL', 'Statement_Date', NULL, NULL, NULL, '33405', 'Using temporary; Using filesort'
'1', 'PRIMARY', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '1164', ''
'1', 'PRIMARY', '<derived4>', 'ALL', NULL, NULL, NULL, NULL, '1139', 'Using where'
'4', 'DERIVED', 'fo_historical_balances_by_account_total', 'index', NULL, 'JPM_Account', '15', NULL, '33405', 'Using where'
'3', 'DERIVED', 'fo_historical_balances_by_account_total', 'index', NULL, 'JPM_Account', '15', NULL, '33405', ''


I've tried forcing an index but it changed nothing. I've tried using CASE instead of IFNULL. I've tried removing every field except one to see if it was the COUNT or CAST that was giving problems. I've tried removing the GROUP BY and WHERE. The only thing that makes it fast is if I remove the COUNT, the GROUP BY, AND the WHERE.

FYI, every other query runs fine.

Any ideas?
Thanks in advance.

Options: ReplyQuote


Subject
Written By
Posted
Please help with making this query quicker
January 05, 2012 03:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.