Great analysis Gregory & Tom... UNION ALL will make a difference.
---------------------------
Here invoices consist of orders, orders consist of order lines. Thus,
each order_id corresponds to just one invoice_id.
One possibility is to add an invoice_id to the order_line. That way the
optimizer need not push anything... the rows will get filtered out early.
Gregory Stark wrote:
> Two things are going wrong.
>
> First, does an order_id belong to precisely one invoice_id? In which case
> instead of grouping just y order_id you need to group by invoice_id,order_id
> and remove the MAX() from around invoice_id. The optimizer can't push the
> invoice_id=? clause down inside the group by because normally to calculate
> max() it needs th entire set of records. It doesn't know there will be only
> one value.
>
> Secondly it seems to me each branch of the union generates distinct values.
> That is there can't be any duplicates or overlap. In which case you can change
> the UNION to a UNION ALL.
>
> There might be more problems but at first glance it looks like the optimizer
> would be able to push the invoice_id=? clause into the subqueries once those
> two changes are made which would throw away the subtotals and reduce to a
> simple index lookup on invoice_id.
