November 17th, 2012, 06:37 PM
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
, 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
, COUNT(xferleads.customer) as countalltime
WHEN credits.authorized = 2 THEN 1
END) as pending
, COUNT( CASE WHEN xferleads.timestamp >= wo_date
AND xferleads.timestamp <= COALESCE(wo_end_date,NOW())
ELSE NULL END ) AS countslw
, COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE
AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY
ELSE NULL END ) AS countdaily
ON customers.id = fulfillment.id
ON xferleads.customer = fulfillment.id
ON fulfillment.repid = vmreps.repid
ON credits.leadid = xferleads.lead_id
WHERE woactive = 1
BY fulfillment.index ) AS v
November 23rd, 2012, 06:34 AM
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.