Index on joined field

SQL++
,
3

Index JOIN might perform better.

Check following indexes and query

CREATE INDEX ix1 ON default(visitType,IFMISSING(isVoided,""), visitId) WHERE SPLIT(META().`id`,':')[0] != "_sync"
CREATE INDEX ix2 ON default(visitId, dateTime) WHERE type = "EVENT";

SELECT MAX(event.dateTime) as dateTime, event.visitId
FROM default visit
JOIN default event ON KEY event.visitId FOR visit
WHERE visit.visitType = "VT_PATIENT_EXTERNE"
AND SPLIT(META(visit).`id`,':')[0] != "_sync"
AND IFMISSING(visit.isVoided,"") IN  [false , ""] AND
event.`type` = "EVENT"
GROUP BY event.visitId;

Instead of SPLIT(META(visit).id,’:’)[0] != “_sync” you can use SUBSTR(META(visit).id,0,5) != “_sync”