Mysql join query improvement and GROUP_CONCAT on GROUP BY

MySQLPHPphpmyadmin

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

Tables:cdr_temp
cdr_temp

transfertable
transfertable

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:

...
    Ifnull(a.transfered, b.transfered)   AS transfer,
    ...
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  ...
GROUP  BY a.lead_id,
          b.uniqueid

-->

...
    Ifnull(a.transfered, 
             ( SELECT substring... FROM transfertable
                          WHERE uniqueid = a.uniqueid
             )
          )   AS transfer,
    ...
FROM   cdr_temp a
   -- gone:  LEFT OUTER JOIN
WHERE  ...
GROUP  BY a.lead_id,
          a.uniqueid  -- Note "a."

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 the PRIMARY KEYs of the tables? Are there any other UNIQUE columns?

Related Question