#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3

    LEFT JOIN is taking FOREVER


    My LEFT JOIN OF credits shown below results in a 30 second query. Taking just that one join out makes it less than a second. This doesn't make sense to me.

    Credits has about 5 columns and 2,000 records, xferleads has about 15 columns with 15,000 records.

    The columns in the problem JOIN are indexed. I have verified this has nothing to do with the SUM
    Code:
    SELECT v.*
         ,        v.countslw / (v.qtyleads + v.freeleads + v.adjustment) * 100 AS pct
         ,       (v.qtyleads + v.freeleads + v.adjustment) / v.daily AS daystofill
         ,       (v.qtyleads + v.freeleads + v.adjustment) / (v.daily + 1) AS daystofill1
         ,       (v.qtyleads + v.freeleads + v.adjustment) / (v.daily + 2) AS daystofill2
         
      FROM ( SELECT fulfillment.index
                  , customers.customer
                  , customers.id
                  , customers.pri
                  , customers.active
                  , customers.daily
                  , qtyleads
                  , priceper
                  , pif
                  , autocharge
                  , adjustment
                  , wo_date
                  , wo_end_date
                  , wo_num
                  , freeleads
                  , woactive
                  , xferleads.timestamp
                  , vmreps.name
                  , credits.authorized
                  , credits.cusid
                  , vmreps.repid
                  , fulfillment.remarks
                  , customers.augment
                  , COUNT(xferleads.customer) as countalltime
                     
                     ,SUM(CASE
                     WHEN credits.authorized = 2 THEN 1
                     ELSE 0
                     END) as pending
                  
              
                      
                  , COUNT( CASE WHEN xferleads.timestamp >= wo_date
                                 AND xferleads.timestamp <= COALESCE(wo_end_date,NOW())
                                THEN xferleads.customer
                                ELSE NULL END ) AS countslw
                  , COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE 
                                 AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY 
                                THEN xferleads.customer
                                ELSE NULL END ) AS countdaily
               FROM fulfillment
             LEFT 
               JOIN customers 
                 ON customers.id = fulfillment.id
             LEFT 
               JOIN xferleads 
                 ON xferleads.customer = fulfillment.id
             LEFT 
               JOIN vmreps 
                 ON fulfillment.repid = vmreps.repid
               
             LEFT
               JOIN credits
                 ON credits.leadid = xferleads.lead_id
                 
              WHERE woactive = 1
             GROUP 
                 BY fulfillment.index ) AS v
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Two suggestions in no particular order:
    Is it a 1to1 relationship or a 1 to many relationship between the credits.leadid = xferleads.lead_id?
    I.e do you get many more rows that needs to be grouped?

    Are you absolutely sure that the two columns in the join:
    credits.leadid = xferleads.lead_id
    are _identical_? If they aren't then MySQL can't use ref index lookup but instead needs to use range and scan the index for every record.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo