For readability:
SELECT tblIR.*, EBirth.EventD AS EBirthD, EDeath.EventD AS EDeathD,
tblNX.Surname AS indexsurname, tblNX.GivenName AS indexgivenname,
tblNX.`Order` AS indextype
FROM tblIR
JOIN tblNX ON tblNX.IDIR=tblIR.IDIR
LEFT JOIN tblER AS EBirth ON (EBirth.IDIR=tblIR.IDIR
AND EBirth.IDType=0
AND EBirth.IDET=3
AND EBirth.Preferred=1
)
LEFT JOIN tblER AS EDeath ON (EDeath.IDIR=tblIR.IDIR
AND EDeath.IDType=0
AND EDeath.IDET=6
AND EDeath.Preferred=1
)
WHERE FLOOR(tblIR.BirthSD/10000)>=1800
AND FLOOR(tblIR.BirthSD/10000)<=1900
AND ((tblNX.Surname='carruthers'
AND tblNX.GivenName>='john')
OR tblNX.Surname>'carruthers'
)
AND tblNX.`Order`>=0
ORDER BY tblNX.`Surname`, tblNX.`GivenName`, EBirth.`EventSD`
LIMIT 50;
I assume
AND ((tblNX.Surname='carruthers'
AND tblNX.GivenName>='john')
OR tblNX.Surname>'carruthers'
)
is for "pagination. It would be more efficient with
AND tblNX.Surname >= 'carruthers'
AND ( tblNX.GivenName >= 'john' OR tblNX.Surname>'carruthers' )
That way, you have AND at the top level of the syntax, not OR.
(I am not convinced that there aren't other things getting in the way, but give that a try.)
Also, you must change
KEY `Surname` (`Surname`),
to
KEY (`Surname`, `GivenName`),
When that fails to help any, then we will need to figure out how to make a subquery specific to isolate and paginate tblnx, _before_ hitting EBirth.
If there are multiple persons with the same EBirth, the LIMIT 50 will sometimes skip/duplicate people!