Try to search your question here, if you can't find : Ask Any Question Now ?

Optimize this query for 1000000+ rows

HomeCategory: stackoverflowOptimize this query for 1000000+ rows
sourav asked 2 weeks ago

I need to pull the data and write it to a csv file but its taking too much time and too much ram. What is wrong with it and what can I do? Also, I feel like there’s a redundancy in the query itself. I’m doing this with PHP.

Here’s the query

CREATE TEMPORARY TABLE temp1 SELECT * FROM vicidial_closer_log
USE INDEX(call_date)
WHERE call_date BETWEEN '1980-01-01 00:00:00' AND '2019-03-12 23:59:59';
CREATE TEMPORARY TABLE temp2 SELECT * FROM vicidial_closer_log
USE INDEX(call_date)
WHERE call_date BETWEEN '1980-01-01 00:00:00' AND '2019-03-12 23:59:59';
SELECT a.call_date,
   a.lead_id,
   a.phone_number
   AS customer_number,
   IF(a.status != 'DROP', 'ANSWERED', 'UNANSWERED')
   AS status,
   IF(a.lead_id IS NOT NULL, 'inbound', 'outbound')
   AS call_type,
   a.USER
   AS agent,
   a.campaign_id
   AS skill,
   NULL
   AS campaign,
   a.status
   AS disposition,
   a.term_reason
   AS Hangup,
   a.uniqueid,
   Sec_to_time(a.queue_seconds)
   AS time_to_answer,
   Sec_to_time(a.length_in_sec - a.queue_seconds)
   AS talk_time,
   Sec_to_time(a.park_sec)
   AS hold_sec,
   Sec_to_time(a.dispo_sec)
   AS wrapup_sec,
   From_unixtime(a.start_epoch)
   AS start_time,
   From_unixtime(a.end_epoch)
   AS end_time,
   c.USER
   AS
   transfered,
   a.comments,
IF(a.length_in_sec IS NULL, Sec_to_time(a.queue_seconds),
Sec_to_time(a.length_in_sec + a.dispo_sec))
   AS duration,
Sec_to_time(a.length_in_sec - a.queue_seconds + a.dispo_sec)
   AS handling_time
FROM   temp1 a
   left outer join temp2 c
                   AND a.uniqueid = c.uniqueid
                   AND a.closecallid < c.closecallid
GROUP  BY a.closecallid

I’ve uploaded screenshot of table structure and the indices.
Table Structure
Indices of Table

Thanks.

1 Answers
Best Answer
Mannu answered 2 weeks ago
Your Answer

2 + 19 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com