Currently:
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 identity.id
order by max_1 desc
limit 91,30\G
That's nasty. It filters on one table, grabs the starttime from the next table, then grabs the GROUP BY value from the third table. At this point, it has a temp table of thousands (millions?) of rows. Now it can start doing the GROUP BY. When that is finished, it still needs to sort to achieve the ORDER BY. Then it skips over 91 rows before delivering 30.
If the relevant columns were all in one table, and you had a good index on it, then (and only then?) could this query run significantly faster.