Do you have any indexes? Is it common that a product can be priced in different currencies over time (assuming the “curr” column is currency)? Can a single invoice have details with different currencies? Can you have an invoice without any details? If not, why are you using a left join between the two? Can you have an invoice detail without a PO? I guess not since the only place the productcode exists is in the PO table. Why is the “amount” calculated using the PO quantity instead of the detail quantity?
I still believe your biggest problem is not 1 but 2 queries within the SELECT clause - which will run for every invoice/detail/po combination. This also explains why it gets considerably slower when not filtering by a specific invoice. This is a schema design problem in my mind so if you’re designing something new I’d rethink it. On the other hand, if it’s existing code and you’re stuck with it then be prepared for inefficient queries and performance.
A second problem has to do with key/filter/join columns. It appears that the productcode may be a string (knowing the length would help) so the join to the history table and the PO is not very efficient. Assuming there’s a product table somewhere you should be using the id OR, at the very least, an indexed numeric column. Filtering by invoiceno also requires a string search but - as long as there’s an index on the column - it isn’t a big deal (although it would still be better if you were using the Primary Key column from the table to filter).
It would be helpful to read the currency and price from the same record in a single look up. Even better if we can get all product information for all details on all invoices (or a single detail on a single invoice without a completely new query). The problems here are that the only way to figure out which price we need is to find the last one prior to the invoice date and the LIMIT clause only works for one product at a time. Using an aggregate will help but a single pass won’t get us all the information we need. So, we need a combination. Something like this maybe:
SELECT i.invoiceno , p.pono , i.issuedate , h.curr , (h.unitprice * p.qty) AS amountFROM invoice AS i INNER JOIN invoicedetail AS d ON i.id = d.invoiceid INNER JOIN po AS p ON d.poid = p.id INNER JOIN ( SELECT li.id AS invoiceid , lp.productcode , MAX(lh.effectivedate) AS lastdate FROM invoice AS li INNER JOIN invoicedetail AS ld ON li.id = ld.invoiceid INNER JOIN po AS lp ON ld.poid = lp.id INNER JOIN pricehistory lh ON lp.productcode = lh.productcode WHERE lh.effectivedate <= li.issuedate AND li.invoiceno = “$invoiceno” GROUP BY li.id , lp.productcode ) AS iph ON i.id = iph.invoiceid AND p.productcode = iph.productcode INNER JOIN pricehistory h ON p.productcode = h.productcode AND iph.lastdate = h.effectivedateWHERE i.invoiceno = “$invoiceno”
You can remove the filters for the invoice number to get all information for all invoices but this may still not be incredibly fast. The subquery that gets the last history date for each invoice/product combination is not really optimized. It can be much more efficient IF the records are entered into the table in date order because you could get the record ID instead of the date which would make the second join to the table much nicer. As mentioned, storing the price and total amount (along with currency) with the detail would make a lot more sense.
Troy