  Ray Mahnahmahna Premium join:2001-04-02 Mesa, AZ
edit: May 11th, @04:47PM
| reply to jayco437 Re: how to write SQL query
That's what I was thinking. It's slow because that subselect is re-run for every row in the outer query. First, I'd create an index on the comment date if there isn't one already and see if that becomes acceptable.
Secondly, I'd try to make a query of the latest comment for each invoice a one-time deal as part of the FROM clause using a table alias. I'd need to create some test data & play with it for a while to actually give you some specific SQL, but something along the lines of
SELECT i.blah, c.comment FROM invoices i JOIN (SELECT most recent comment for each invoice) c ON (c.invoice_id = i.invoice_id) -- ON DELETE CASCADE |