Re: Query optimizing

Sebastian Ritter wrote:
> A lot of the reports our technical officers submit to us include a listing
> of all actioned issues for a given day along with the last modified followup
> of each said issue. With the number of rows in our database increasing at a
> high rate, these queries are starting to run too slowly.

> We have identified that the slowness in our queries is trying to return the
> lastest followup for each actioned issue that day. Without further ado here
> are two variations I have tried within the system (neither of which are
> making the cut):
> 
> V1 (correlated subquery - Very bad performance)
> 
>  (SELECT
>          fu.*
>   FROM
>         manage_followup fu,
>         manage_issue i
>   WHERE
>          i.id = fu.n_issue
>          AND
>          fu.id = (SELECT
>                         id
>                    FROM
>                         manage_followup
>                     WHERE
>                          n_issue = i.id
>                     ORDER BY
>                          dt_modified DESC
>                     LIMIT 1)) AS latestfu,
> 

Do you have an index on (id,dt_modified) for manage_followup? Can you
provide an EXPLAIN ANALYSE for this?

--  Richard Huxton Archonet Ltd