SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing this query.

    Hi,

    Can anyone point out how I should optimize this query:
    PHP Code:
    SELECT 
                    c
    .*, 
                    
    cl.*, 
                    
    clc.countyName as clientCounty
                    
    cause.causeTitle,
                    
    status.statusTitle
                    
                FROM 
    ".DB_PREFIX."claims c
                    INNER JOIN
                        
    ".DB_PREFIX."clients cl
                    ON 
                        c
    .clientID cl.clientID
                    INNER JOIN 
                        
    ".DB_PREFIX."counties clc 
                    ON 
                        clc
    .countyID cl.clientCountyID
                    INNER JOIN 
                        
    ".DB_PREFIX."causecodes cause 
                    ON
                        cause
    .causeID c.claimCauseCode
                        
                    INNER JOIN 
                        
    ".DB_PREFIX."statuscodes status 
                    ON     
                        status
    .statusID c.claimStatusCode
                        
    ";
                    /* Drag in the specific claims */    
                    
                    
    $q .= "LEFT JOIN 
                        
    ".DB_PREFIX."liabilityclaims li 
                    ON c
    .claimID li.claimID
                    
                    LEFT JOIN 
                        
    ".DB_PREFIX."motorclaims mc 
                    ON c
    .claimID mc.claimID
                    
                    LEFT JOIN 
                        
    ".DB_PREFIX."propertyclaims pc 
                    ON c
    .claimID pc.claimID
                        
    ";
                    /* Now join the solicitor onto the property claim and liability claims */
                    
                    
    $q .= "LEFT JOIN 
                        
    ".DB_PREFIX."solicitors solicitors
                    ON 
    (solicitors.solicitorID li.solicitorID OR solicitors.solicitorID pc.solicitorID)
                        
    ";
                    /* Now join the loss adjuster, same as above */
                    
                    
    $q .= "LEFT JOIN 
                        
    ".DB_PREFIX."lossadjusters loss
                    ON 
    (loss.lossAdjusterID pc.lossAdjusterID OR loss.lossAdjusterID li.lossAdjusterID
                        
    ";
                    /* Join the third part for motorclaims */
                    
                    
    $q .= "LEFT JOIN 
                        
    ".DB_PREFIX."thirdparties third
                    ON third
    .thirdPartyID mc.thirdPartyID
                        
                        
                WHERE 1 
    AND c.claimDeleted 
    I know the problem is
    PHP Code:
    LEFT JOIN 
                        
    ".DB_PREFIX."lossadjusters loss
                    ON 
    (loss.lossAdjusterID pc.lossAdjusterID OR loss.lossAdjusterID li.lossAdjusterID
    according the the mysql bug #17164 http://bugs.mysql.com/bug.php?id=17164

    I'm just not sure how I could rearrange this.

    Cheers,
    Rhys

  2. #2
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've changed to this:

    PHP Code:
    /* Now join the loss adjuster, same as above */
                    
                    
    $q .= "LEFT JOIN 
                        "
    .DB_PREFIX."lossadjusters losspc
                    ON (losspc.lossAdjusterID = pc.lossAdjusterID) 
                        "
    ;
                    
    /*For optimisation purposes*/
                    
    $q .= "LEFT JOIN 
                        "
    .DB_PREFIX."lossadjusters lossli
                    ON (lossli.lossAdjusterID = li.lossAdjusterID) 
    Is there anything wrong with doing this?

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by TacMaf View Post
    I know the problem is
    PHP Code:
    LEFT JOIN 
                        
    ".DB_PREFIX."lossadjusters loss
                    ON 
    (loss.lossAdjusterID pc.lossAdjusterID OR loss.lossAdjusterID li.lossAdjusterID
    according the the mysql bug #17164 http://bugs.mysql.com/bug.php?id=17164
    That bug was from 2006 and seems to be resolved (reading the comments on that page).

    What problem are you having with this query?

    Did you do an EXPLAIN?

  4. #4
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It was an optimisation problem.
    OPTIMISE.png

    Indexing didn't seem to help.
    Attached Images Attached Images

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by TacMaf View Post
    Is there anything wrong with doing this?
    depends on the one-to-many aspects of the repaltionship

    you did not give any information in this regard, so i can't really tell
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member
    Join Date
    Oct 2011
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What problem do this thing?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kuangren View Post
    What problem do this thing?
    you mean "repaltionship" ??

    yeah, that was a pretty bad typo
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •