MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Rick James
Date: November 19, 2010 07:14PM

Oh, a closer look at your schema shows that id and IDENTID are 1:1, so we could change the GROUP BY to
select  identity.id,
        max(intervention.starttime) as max_1
    from  identity
    join  intervention on identity.IDENTID=intervention.PATIENTID
    join  data on data.INTID=intervention.INTID
    where  data.expiry is null
       or  data.expiry >= "2010-11-05 15:55"
    group by  intervention.PATIENTID       -- Here
    order by  max_1 desc
    limit  91,30\G

Then, to avoid fetching id lots of times,...
SELECT i.id,   -- Now this is fetched only 30 times
       max_1
    FROM  identity AS i
    JOIN (
        SELECT  iv.PATIENTID,
                max(intervention.starttime) as max_1
            FROM  intervention AS iv
            JOIN  data AS d on d.INTID = iv.INTID
            WHERE  d.expiry is null
               OR  d.expiry >= "2010-11-05 15:55"
            group by  iv.PATIENTID
            order by  max_1 desc
            limit  91,30
         ) AS x  ON i.IDENTID = x.PATIENTID
    ORDER BY max_1 DESC   -- just in case

Options: ReplyQuote


Subject
Views
Written By
Posted
1359
November 06, 2010 10:56AM
1369
November 07, 2010 01:05PM
1905
November 19, 2010 07:05PM
1293
November 19, 2010 07:20PM
Re: slow query sorting on group maximum
1292
November 19, 2010 07:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.