I have two tables which I need to perform a left outer join on to get the result set I want. The query I have now works but its taking too long to process. Any suggestions?
Query:
SELECT Date_format(a.call_date, '%Y-%m-%d') Call_Date,
Date_format(a.call_date, '%H:%i:%s') Call_Time,
a.lead_id,
customer_number,
status,
a.call_type,
agent,
skill,
campaign,
disposition,
hangup,
a.uniqueid,
time_to_answer,
talk_time,
hold_sec,
wrapup_sec,
Date_format(start_time, '%H:%i:%s') Start_Time,
Date_format(end_time, '%H:%i:%s') End_Time,
Ifnull(a.transfered, b.transfered) AS transfer,
comments,
location,
duration,
handling_time,
number_dialed AS DID
FROM cdr_temp a
LEFT OUTER JOIN (SELECT USER,
Substring(number_dialed, 18, 11) AS transfered,
uniqueid
FROM transfertable)
b
ON a.uniqueid = b.uniqueid
WHERE a.call_date BETWEEN '2019-01-01 00:00:00' AND '2019-03-23 00:00:00'
GROUP BY a.lead_id,
b.uniqueid
Index:
uniqueid and call_date for transfertable
uniqueid and lead_id for cdr_temp
Explain on query
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL NULL NULL NULL NULL 1333 Using where; Using temporary; Using filesort
1 SIMPLE transfertable ref uniqueid uniqueid 22 test.a.uniqueid 1
PS: I need to concatenate skills and number_dialed on GROUP BY. I've tried using GROUP_CONCAT but it didn't work and I have no idea why.
UPDATE: I've tried this query using a temp table. Is it better?
CREATE TEMPORARY TABLE transfertable_temp (
INDEX idx (uniqueid, transfered)
)
SELECT uniqueid, SUBSTRING(number_dialed, 18, 11) AS transfered
FROM transfertable WHERE call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59';
SELECT
DATE_FORMAT(a.call_date, '%Y-%m-%d') Call_Date,
DATE_FORMAT(a.call_date, '%H:%i:%s') Call_Time,
a.lead_id,
customer_number,
status,
a.call_type,
agent,
GROUP_CONCAT(skill SEPARATOR ';') AS Skill,
campaign,
disposition,
hangup,
a.uniqueid,
time_to_answer,
talk_time,
hold_sec,
wrapup_sec,
Date_format(start_time, '%H:%i:%s') Start_Time,
Date_format(end_time, '%H:%i:%s') End_Time,
IFNULL(a.transfered, b.transfered) transfer,
comments,
location,
duration,
handling_time,
number_dialed AS DID
FROM cdr_temp a
LEFT JOIN transfertable_temp b
ON a.uniqueid = b.uniqueid
WHERE a.call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59'
GROUP BY a.lead_id,
b.uniqueid
Best Answer
Generally, you should avoid
JOIN ( SELECT ... )
. In the past it was notoriously inefficient. In later versions it is sometimes somewhat slower than alternatives.Instead, let's get rid of the
JOIN
some of the time:-->
It is "improper" to
GROUP BY
only some of the non-aggregate columns; you will get random values for the others. Is a "skill" associated with a "lead"?It would be more informative to have
SHOW CREATE TABLE
for each table. What are thePRIMARY KEYs
of the tables? Are there any otherUNIQUE
columns?