MySQL Forums
Forum List  »  Performance

Re: SELECT never completes
Posted by: Rick James
Date: July 09, 2015 12:55AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
1582
July 06, 2015 10:08PM
774
July 06, 2015 11:51PM
1239
July 07, 2015 02:16AM
Re: SELECT never completes
739
July 09, 2015 12:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.