If two tables are in a 1:1 relationship, why have them as separate tables? Unnecessary JOINs sometimes cost a lot (sometimes, they are perfectly fine).
There would probably be a small gain via (on `data`):
INDEX(expiry, INTID)
(This assumes you got rid of "OR expiry IS NULL".)
Why do you join to `event` at all? No fields of it are used. If you are checking for the existence of an event row before considering the patient, then ponder replacing the JOIN with an additional WHERE clause:
AND EXISTS ( SELECT * FROM event WHERE ... )
OUCH! I'm blind -- this seems worse than useless; it is a cross-join!:
join event on identity.IDENTID=intervention.PATIENTID
1.2G is reasonable for a 2G machine; be sure you are never swapping.
The NULLs, DISTINCT, etc, were to clean up the data/schema enough to then try to optimize it. Could you show me the schema, etc, after those changes:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
As for the distinction between a 4-byte artificial PK and a 16-byte UNIQUE key being used as a PK... Yes, there are tradeoffs. At this point, I don't have enough details to make a string argument either way. (NULLness, length, number of other indexes, use in WHERE, use in GROUP/ORDER BY, etc, of the UNIQUE key are factors that weigh into this discussion.)