Re: slow query sorting on group maximum

[Rick James]

select  distinct identity.id, max(intervention.starttime) as max_1
    from  identity
    join  event 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  identity.id
    order by  max_1 desc
    limit  91,30;

DISTINCT and GROUP BY -- usually redundant.
LIMIT and OFFSET; smells like pagination?
'OR' virtually eliminates any chance of using an INDEX; can you get rid of NULLs in `expiry`?
Other fields, such as PATIENTID can be NULL; is that reasonable?
For intervention, why have an AUTO_INCREMENT, when you seem to have a perfectly usable UNIQUE key of INTID?
Is this query a subquery in something else?
Are any pairs of tables 1:1?
Is innodb_buffer_pool_size set to 70% of _available_ RAM?