Re: slow query sorting on group maximum

[Rick James]

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