我有两个表,我需要对它们执行左外部联接,以获得我想要的结果集。我的查询现在可以工作了,但是处理起来花费的时间太长了。有什么建议吗?
查询:
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.uniqueidTables:cdr_temp

可转移

索引:
uniqueid and call_date for transfertable
uniqueid and lead_id for cdr_temp查询时解释
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:我需要在GROUP BY上连接skills和number_dialed。我试过使用GROUP_CONCAT,但它不起作用,我也不知道为什么。
发布于 2019-03-25 13:38:19
我真的不明白transfertable上子查询的意义。但是,仔细观察该子查询,我可以看到您在那里调用了SUBSTRING。这几乎肯定意味着MySQL将不能使用任何索引来改进该连接。这里的一种方法是创建一个临时表,其中包含number_dialed的子字符串作为真正的列。
CREATE TEMPORARY TABLE transfertable_temp (
INDEX idx (uniqueid, transfered)
)
SELECT uniqueid, SUBSTRING(number_dialed, 18, 11) AS transfered
FROM transfertable;如果MySQL选择使用这个临时表(而不是MySQL还不支持的物化视图),那么连接到这个临时表应该会提高连接的性能。请注意,我还在索引中包括了子字符串数字,以涵盖正在使用的另一列。
以下是您更新的查询:
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) transfer,
comments,
location,
duration,
handling_time,
b.number_dialed DID
FROM cdr_temp a
LEFT JOIN transfertable_temp 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毫无意义,尤其是因为您甚至没有调用任何聚合函数。如果您认为您需要聚合,请编辑您的问题并告诉我们原因。
https://stackoverflow.com/questions/55331660
复制相似问题