0

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
  • I do not see GROUP_CONCAT in your query. Try to simplify the query (remove all fields except those which are used in JOIN/WHERE/GROUP BY/AGGREGATE/PRIMARY KEYS). – Akina Mar 26 at 7:56
  • I've edited the query and added the GROUP_CONCAT, please check above – on3ss Mar 26 at 8:22
  • What table the argument skill is taken from? transfertable_temp? – Akina Mar 26 at 8:41
  • its taken from cdr_temp but it is there in transfertable table also – on3ss Mar 26 at 8:44
  • If it is taken from cdr_temp where uniqueid is unique, than all values of cdr_temp.skill which are GROUP_CONCATed are the same... – Akina Mar 26 at 8:47
0

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?

  • I've cross-checked and yes GROUP BY should not be there to get the whole information. – on3ss Apr 25 at 7:56
  • @on3ss - Read about only_full_group_by – Rick James Apr 25 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.