Оптимизировать этот запрос на 1000000+ строк -- php поле с участием mysql поле с участием pdo поле с участием web-applications поле с участием crud пол Связанный проблема

Optimize this query for 1000000+ rows


0
vote

проблема

русский

Мне нужно потянуть данные и записывать его в файл CSV, но слишком много времени и слишком много времени. Что не так с этим и что я могу сделать? Кроме того, я чувствую, что есть избыточность в самом запросе. Я делаю это с PHP.

Вот запрос

 <код> 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                    ON a.uniqueid = c.uniqueid                    AND a.closecallid < c.closecallid GROUP  BY a.closecallid   

Я загрузил скриншот структуры таблицы и индексов. Столовая структура Индексы таблицы

спасибо.

Обновление: Показать table table vicidial_closer_log

 <код> vicidial_closer_log     CREATE TABLE `vicidial_closer_log` ( `closecallid` int(9) unsigned NOT NULL AUTO_INCREMENT, `lead_id` int(9) unsigned NOT NULL, `list_id` bigint(14) unsigned DEFAULT NULL, `campaign_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `call_date` datetime DEFAULT NULL, `start_epoch` int(10) unsigned DEFAULT NULL, `end_epoch` int(10) unsigned DEFAULT NULL, `length_in_sec` int(10) DEFAULT NULL, `status` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `phone_code` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, `phone_number` varchar(18) COLLATE utf8_unicode_ci DEFAULT NULL, `user` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `comments` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `processed` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL, `queue_seconds` decimal(7,2) DEFAULT 0.00, `user_group` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `xfercallid` int(9) unsigned DEFAULT NULL, `term_reason`       enum('CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','HOLDRECALLXFER',    'HOLDTIME','NOAGENT','NONE','MAXCALLS') COLLATE utf8_unicode_ci DEFAULT   'NONE',  `uniqueid` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',  `agent_only` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',  `queue_position` smallint(4) unsigned DEFAULT 1,  `called_count` smallint(5) unsigned DEFAULT 0,  `nopaperform` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',  `park_sec` int(3) DEFAULT 0,  `dispo_sec` int(3) DEFAULT 0,  `record_file` text COLLATE utf8_unicode_ci DEFAULT NULL,  PRIMARY KEY (`closecallid`),  KEY `lead_id` (`lead_id`),  KEY `call_date` (`call_date`),  KEY `campaign_id` (`campaign_id`),  KEY `uniqueid` (`uniqueid`),  KEY `phone_number` (`phone_number`),  KEY `date_user` (`call_date`,`user`),  KEY `closecallid` (`closecallid`) ) ENGINE=MyISAM AUTO_INCREMENT=1850672 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci   

Объясните запрос (только на третьем запросе):

 <код> id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra    1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    664640  Using temporary; Using filesort 1   SIMPLE  c   ALL     NULL    NULL    NULL    NULL    662480  Using where; Using join buffer (flat, BNL join)   

Обновление (обновленное запрос):

 <код> 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   vicidial_closer_log a        LEFT OUTER JOIN vicidial_closer_log c                     ON a.closecallid <> c.closecallid                        AND a.uniqueid = c.uniqueid                        AND a.closecallid < c.closecallid WHERE a.call_date BETWEEN '2018-01-01 00:00:00' AND '2019-03-13 23:59:59'   

Объясните на обновленном запросе:

 <код> id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra    1   SIMPLE  a   ALL     call_date,date_user     NULL    NULL    NULL    662829  Using where 1   SIMPLE  c   ref     PRIMARY,uniqueid,closecallid    uniqueid    62  aastell_bliss.a.uniqueid    1   Using where   

Обновленное результат выполнения запросов:

 <код> Number of rows present between given time range: 155016 rows Time taken: 0.0149 secs   

Это работает!

Английский оригинал

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                    ON 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.

UPDATE: SHOW CREATE TABLE vicidial_closer_log

vicidial_closer_log     CREATE TABLE `vicidial_closer_log` ( `closecallid` int(9) unsigned NOT NULL AUTO_INCREMENT, `lead_id` int(9) unsigned NOT NULL, `list_id` bigint(14) unsigned DEFAULT NULL, `campaign_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `call_date` datetime DEFAULT NULL, `start_epoch` int(10) unsigned DEFAULT NULL, `end_epoch` int(10) unsigned DEFAULT NULL, `length_in_sec` int(10) DEFAULT NULL, `status` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `phone_code` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, `phone_number` varchar(18) COLLATE utf8_unicode_ci DEFAULT NULL, `user` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `comments` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `processed` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL, `queue_seconds` decimal(7,2) DEFAULT 0.00, `user_group` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `xfercallid` int(9) unsigned DEFAULT NULL, `term_reason`       enum('CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','HOLDRECALLXFER',    'HOLDTIME','NOAGENT','NONE','MAXCALLS') COLLATE utf8_unicode_ci DEFAULT   'NONE',  `uniqueid` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',  `agent_only` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',  `queue_position` smallint(4) unsigned DEFAULT 1,  `called_count` smallint(5) unsigned DEFAULT 0,  `nopaperform` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',  `park_sec` int(3) DEFAULT 0,  `dispo_sec` int(3) DEFAULT 0,  `record_file` text COLLATE utf8_unicode_ci DEFAULT NULL,  PRIMARY KEY (`closecallid`),  KEY `lead_id` (`lead_id`),  KEY `call_date` (`call_date`),  KEY `campaign_id` (`campaign_id`),  KEY `uniqueid` (`uniqueid`),  KEY `phone_number` (`phone_number`),  KEY `date_user` (`call_date`,`user`),  KEY `closecallid` (`closecallid`) ) ENGINE=MyISAM AUTO_INCREMENT=1850672 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

EXPLAIN QUERY(On third query only):

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra    1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    664640  Using temporary; Using filesort 1   SIMPLE  c   ALL     NULL    NULL    NULL    NULL    662480  Using where; Using join buffer (flat, BNL join) 

UPDATE(Updated Query):

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   vicidial_closer_log a        LEFT OUTER JOIN vicidial_closer_log c                     ON a.closecallid <> c.closecallid                        AND a.uniqueid = c.uniqueid                        AND a.closecallid < c.closecallid WHERE a.call_date BETWEEN '2018-01-01 00:00:00' AND '2019-03-13 23:59:59' 

EXPLAIN on updated query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra    1   SIMPLE  a   ALL     call_date,date_user     NULL    NULL    NULL    662829  Using where 1   SIMPLE  c   ref     PRIMARY,uniqueid,closecallid    uniqueid    62  aastell_bliss.a.uniqueid    1   Using where 

Updated Query Execution Result:

Number of rows present between given time range: 155016 rows Time taken: 0.0149 secs 

It works!

</div
              
         
         

Список ответов

0
 
vote
vote
Лучший ответ
 

Сводка комментариев, которые приводят к ответу:

    .
  • <код> CREATE TEMPORARY TABLE ... SELECT не создает индексов на временную таблицу
  • Явное использование временной таблицы, особенно большого размера, редко будет давать усиление производительности.
  • Использование псевдонимов таблицы в присоединении позволяет я присоединиться к себе
  • Группа по первичному ключу на левой стороне соединения не добавляет не так уникально, и <код> JOIN не имел совокупных выражений. <Код> GROUP BY Добавляет неявную <код> web-app0 , чтобы вы, что вы, выражение, можете в конечном итоге более медленнее, если бы вторичный индекс был использован для присоединения к таблице.
  • , в то время как диапазон даты запроса был большой, подготавливая для него значительный фильтр, когда маленький сделает <код> web-app1 более благоприятным в качестве индекса. Чтобы сделать это более благоприятным, ключевой ключ соединения добавляется в конец индекса, поэтому большая часть работы присоединения может произойти, просто глядя на индекс.
  • Когда pk находится на столбце, вторичный индекс в том же колонке не требуется.
 

Summary of comments that lead to an answer:

  • CREATE TEMPORARY TABLE ... SELECT doesn't create indexes on the temporary table
  • Explicit use of a temporary table, particularly of a large size, will rarely give a performance gain.
  • Using table aliases in a join allows for a self join
  • Group by Primary Key on the left side of a join doesn't add much as its already unique and the JOIN had no aggregate expressions. GROUP BY adds an implicit ORDER BY so you expression could end up slower if a secondary index was used to join the table.
  • While the date range of the query was large, preparing for it to be a significant filter when small would make the call_date more favourable as an index. To make this more favorable, the join key is added to the end of the index so most of the work of the join can happen by just looking at the index.
  • When PK is on a column, a secondary index on the same column isn't needed.
</div
 
 

Связанный проблема

13  Отправьте данные FORM HTML в базу данных SQL через PHP (используя mysqli)  ( Send html form data to sql database via php using mysqli ) 
Я хочу отправить данные, введенные в HTML-форму в мою базу данных SQL, то есть создайте новую строку, приписываю определенные значения в определенные столбцы....

1  Иерархическая система комментариев в резьбовом режиме: php и mysql  ( Hierarchical commenting system in threaded view php and mysql ) 
У меня есть необходимость оказать резьбовое изображение 2-уровневых иерархических комментариев системы. Комментарии хранятся в базе данных. Информация о иерар...

0  Создание копии Symfony Project на сервере?  ( Making a copy of symfony project on server ) 
Я пытаюсь скопировать существующий проект Symfony в другой каталог, поэтому я могу проверить изменения, прежде чем применять их в живую версию. Я получаю ош...

0  Yii2 - Есть ли способ написать основные символы в модели поиска и результаты показывают все запросы как базовыми, так и специальными символами?  ( Yii2 is there a way to write basic characters in search model and results show ) 
Просто сказать это сначала, я относительно новый в Yii2, так что спасибо за понимание. Я использую Mariadb Database Engine. Например, в QueryParams я писа...

0  Как я могу получить ответ JSON обратно (PHP)  ( How can i get json response back php ) 
Я сталкиваюсь с проблемой с ответом сервера. Моя цель состоит в том, чтобы отправить некоторые данные на сервер и получить некоторые ответы с учетом отправлен...

0  Mac Yosmite: Установите MacPorts Apache в качестве по умолчанию (Opt / local / apache2) на localhost, а не распределение OSX  ( Mac yosmite set macports apache as default opt local apache2 on localhost rat ) 
Я установил Apache через MACPPORTS, он работал нормально, но вдруг по какой-то причине он переключился на использование распределения OSX по умолчанию Apache,...

1  Передайте переменную из исходного файла в включенный файл в PHP  ( Pass a variable from the source file to an included file in php ) 
Для моего сайта я хочу хранить общий формат сайта в одном файле PHP в одном месте, и каждый из разных страниц содержимого в местном месте страницы. Затем я хо...

0  Добавление поля в запросе MySQL  ( Adding a field in a mysql query ) 
Мне нужно добавить поле в одном из наших запросов. Я NT PHP-программист купил, я немного ударил. Запрос: <код> if (_QUERYSTRING_) { switch ($intMode) { ...

1  Уменьшите часы экземпляра CloudsQL (WordPress)  ( Reduce cloudsql instance hours wordpress ) 
У меня есть установка WordPress на GAE. Это веб-сайт, который будет очень редко обновлен, и будет иметь мало посетителей каждый день (100 ish max). Я хотел бы...

8  MySQL Заказ с использованием строки даты данных  ( Mysql order by using date data row ) 
У меня есть запрос что-то подобное: <код> public class AlarmReciever extends BroadcastReceiver { AlarmClock alarmClock=new AlarmClock(); @Override public v...

0  Я хочу получить изображение, удивляемые в PHP, нажав кнопку «Отправить», когда я пробую приведенный ниже код, получая ошибку для ISSET [Закрыто]  ( I want to get the image cordinates in php on clicking the submit button when i ) 
<в сторону CLASS = "S-NEWACTS S-WELTIVE__info JS-Post-New Imide MB16« Роль = «Статус»> закрыто. Этот вопрос не воспроизводится или был вызван опечаткам...

-1  PHP Удалить окружающие пустые массивы из массива меню  ( Php remove surrounding empty arrays from menu array ) 
Привет, я пытаюсь построить лучшее динамическое меню, чем тот, который у меня в настоящее время использует массивы У меня нет проблем с удалением окружающих...

0  Google Map не отображает  ( Google map not displaying ) 
У меня есть карта Google, которая используется здесь: http://www.comehike.com/outdoors/birds/birds.php Теперь я вижу сообщения об ошибках .j для 1) Инициа...

1  Заказ в порядке без использования событий  ( Order in order without using events ) 
Моя таблица MySQL имеет следующую структуру: <код> +------+----------+-----+-----------+----------------+----------+ | uuid | username | ... | clan_rank | u...

2  Laravel 5.2 Hasthrough  ( Laravel 5 2 hasthrough ) 
Так что я пытаюсь получить доступ к пользователям заметки по их аккаунтам. В основном Notes не принадлежат пользователю, они принадлежат к учетной записи, в к...

Связанный проблема

13  Отправьте данные FORM HTML в базу данных SQL через PHP (используя mysqli) 
1  Иерархическая система комментариев в резьбовом режиме: php и mysql 
0  Создание копии Symfony Project на сервере? 
0  Yii2 - Есть ли способ написать основные символы в модели поиска и результаты показывают все запросы как базовыми, так и специальными символами? 
0  Как я могу получить ответ JSON обратно (PHP) 
0  Mac Yosmite: Установите MacPorts Apache в качестве по умолчанию (Opt / local / apache2) на localhost, а не распределение OSX 
1  Передайте переменную из исходного файла в включенный файл в PHP 
0  Добавление поля в запросе MySQL 
1  Уменьшите часы экземпляра CloudsQL (WordPress) 
8  MySQL Заказ с использованием строки даты данных 
0  Я хочу получить изображение, удивляемые в PHP, нажав кнопку «Отправить», когда я пробую приведенный ниже код, получая ошибку для ISSET [Закрыто] 
-1  PHP Удалить окружающие пустые массивы из массива меню 
0  Google Map не отображает 
1  Заказ в порядке без использования событий 
2  Laravel 5.2 Hasthrough 



© 2021 www.qaru.top All Rights Reserved. Q&A House все права защищены


Licensed under cc by-sa 3.0 with attribution required.