MySQL Forums
Forum List  »  MySQL Query Browser

Re: Please help with making this query quicker
Posted by: kordirko kordirko
Date: January 07, 2012 07:56AM

Hello,

It would be helpful if you showed a definition of a view margin_date,
and short description of what results your are query is supposed to provide, in logical terms (what is a business requirement ?).
Also tell us which exactly fields are indexed (are margin_date.JPM_Accountfo_historical_balances_by_account_total.Statement_Date indexed ?).
Show indexed fields of tables "hidden" under the view as well.

Some general thoughts:
First - the only condition used in the WHERE clause cannot use any indexes. Look at it:
   SELECT ...
   FROM x LEFT JOIN y ON .....
   WHERE x.date < y.date
this condition canon be evaluated before joining two tables (thus filter out rows to be joined to minimize cost of joining), because
you must first join rows from x and y to know x.date and y.date. So the where condition is not applied before join, but filters row from result of join.
In other words, in this query the full table scan of X always must be done,
then each row of X is joined to the view y, then results are filtered by the WHERE clause, and finally an aggregation is performed (group by).

Second - views have not any indexes, only tables have,
views are good in performing simple 'select ... from view), but it's better
to avoid joining views in other queries.
Try to rewrite this query not using the view, but direct tables "hidden" under this view.

Options: ReplyQuote


Subject
Written By
Posted
Re: Please help with making this query quicker
January 07, 2012 07:56AM


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.