If the result set of a query does not use a field produced by a subquery or view, is that field still processed by the subquery? I have a lot of views in my database that are used in many places, but the exact set of fields needed varies by context. Rather than duplicating similar queries in each context (with the appropriate field group), I've been using views that may contain extra fields for each context. It looks like these extra views do affect performance in MySQL (I experience a 1/4 improvement in performance by removing the extra field in the example below). Any solutions? Different database (i.e. Oracle)?
See below for an example; the charge_off_date field could clearly be skipped when running the view, but it still affects performance.
select
l.loan_id, last_payment_date
from
loans as l
left join
(select
loan_id,
max(ph.received_d) as last_payment_date,
max(case
when ph.co = 1 then coalesce(ph.received_d, ph.month)
else null
end) as charge_off_date
from
payment_history as ph) as payment_stats ON payment_stats.loan_id = l.loan_id
MySQL instantiates (non-correlated) subqueries and creates an execution plan for views that is not optimized beyond the view. In other words, MySQL will not optimize in this case.
Other databases are smarter about optimization. That does not mean they will do the right thing, but there is a chance.
I would suggest, though, that you write the query as just a join with an aggregation and no subquery:
select l.loan_id, max(ph.received_d) as last_payment_date,
max(case when ph.co = 1 then coalesce(ph.received_d, ph.month)
end) as charge_off_date
from loans l left join
payment_history ph
on ph.loan_id = l.loan_id
group by l.loan_id;
There is no need for the subquery in the from clause (and this isn't allowed in a MySQL view anyway). Plus, if you have a where clause, this should take advantage of the where clause and improve performance.