MySQL - 慢多子查询和 GROUP BY

Question

当使用 GROUP BY 运行以下查询时，它会花费太多时间。

 SELECT specialities.id AS ID_DEPARTMENT, 
        specialities.name AS ID_DEPARTMENT_NAME, 
        agenda.idagenda AS ID_SERVICE, 
        agenda.name AS ID_SERVICE_NAME, 
        supervisor.clients_waiting AS CWaiting,
        IFNULL(supervisor.clients_resent_waiting_area, 0) AS CWaiting_Resent_Area,
        supervisor.clients_attending AS CAttending,
        supervisor.clients_attended AS CAttended,
        (SELECT SUM(TIME_TO_SEC(TIMEDIFF(NOW(), time_waiting)) / CWaiting) 
         FROM supervisor_time_data 
         WHERE supervisor_time_data.id_service = supervisor.id_service) AS TME, 
        (SELECT SUM(TIME_TO_SEC(TIMEDIFF(NOW(), time_attending)) / CAttending) 
         FROM supervisor_time_data 
         WHERE supervisor_time_data.id_service = supervisor.id_service) AS TMA, 
        (SELECT TIME_TO_SEC(MAX(TIMEDIFF(NOW(), time_waiting))) 
         FROM supervisor_time_data 
         WHERE supervisor_time_data.id_service = supervisor.id_service) AS MTE, 
        (SELECT TIME_TO_SEC(MAX(TIMEDIFF(NOW(), time_attending))) 
         FROM supervisor_time_data 
         WHERE supervisor_time_data.id_service = supervisor.id_service) AS MTA,
        supervisor.tme_accumulated AS TME_ACCUMULATED,
        supervisor.tma_accumulated AS TMA_ACCUMULATED
 FROM supervisor, supervisor_time_data, agenda, specialities
 WHERE supervisor.id_service = agenda.id 
   AND supervisor_time_data.id_service = supervisor.id_service 
   AND agenda.idspeciality = specialities.id
   AND supervisor.booked_or_sequential = 0
   AND supervisor.id_service IN (1,2,3) 
 GROUP BY supervisor.id_service
 ORDER BY agenda.name ASC;

它需要 3 多秒，在注释 GROUP BY 行时需要 8 毫秒。

关于如何优化此查询的任何想法？

谢谢

编辑:

CREATE TABLE `supervisor` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `id_department` int(6) DEFAULT NULL,
  `id_service` int(9) DEFAULT NULL,
  `clients_waiting` int(6) DEFAULT '0',
  `clients_attending` int(6) DEFAULT '0',
  `clients_attended` int(6) DEFAULT '0',
  `tma_accumulated` int(9) DEFAULT '0',
  `tme_accumulated` int(9) DEFAULT '0',
  `clients_resent_waiting_area` int(6) DEFAULT NULL,
  `booked_or_sequential` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_service` (`id_service`),
  KEY `booked_or_sequential` (`booked_or_sequential`),
  KEY `clients_waiting` (`clients_waiting`)
) ENGINE=MyISAM AUTO_INCREMENT=172 DEFAULT CHARSET=latin1;

.

CREATE TABLE `supervisor_time_data` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `id_ogs` int(32) DEFAULT NULL,
  `booked_or_sequential` tinyint(1) DEFAULT NULL,
  `time_waiting` datetime DEFAULT NULL,
  `time_attending` datetime DEFAULT NULL,
  `status` int(2) DEFAULT '0',
  `id_service` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_service` (`id_service`),
  KEY `time_waiting` (`time_waiting`),
  KEY `time_attending` (`time_attending`),
  KEY `booked_or_sequential` (`booked_or_sequential`)
) ENGINE=MyISAM AUTO_INCREMENT=2281 DEFAULT CHARSET=latin1;

.

CREATE TABLE `agenda` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `idagenda` varchar(255) DEFAULT NULL,
  `iduser` int(3) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `agendatype` tinyint(4) DEFAULT NULL,
  `idspeciality` int(6) DEFAULT NULL,
  `denomination` varchar(255) DEFAULT NULL,
  `ticket_count` int(3) DEFAULT NULL,
  `waiting` int(3) DEFAULT NULL,
  `ticket_start` int(3) DEFAULT NULL,
  `ticket_end` int(3) DEFAULT NULL,
  `ticket_letter` varchar(12) DEFAULT NULL,
  `idcenter` int(9) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idagenda` (`idagenda`),
  KEY `idspeciality` (`idspeciality`)
) ENGINE=MyISAM AUTO_INCREMENT=2228 DEFAULT CHARSET=latin1;

.

CREATE TABLE `specialities` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `idwaitingarea` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=149 DEFAULT CHARSET=latin1;

Answer 1

这是您的连接查询。如果这不起作用，请发布您的架构。

SELECT
  specialities.id              AS ID_DEPARTMENT,
  specialities.name            AS ID_DEPARTMENT_NAME,
  agenda.idagenda              AS ID_SERVICE,
  agenda.name                  AS ID_SERVICE_NAME,
  supervisor.clients_waiting   AS CWaiting,
  IFNULL(supervisor.clients_resent_waiting_area, 0) AS CWaiting_Resent_Area,
  supervisor.clients_attending AS CAttending,
  supervisor.clients_attended  AS CAttended,
  SUM(TIME_TO_SEC(TIMEDIFF(NOW(), supervisor_time_data.time_waiting)) / supervisor.clients_waiting) AS TME,
  SUM(TIME_TO_SEC(TIMEDIFF(NOW(), supervisor_time_data.time_attending)) / supervisor.clients_attending) AS TMA,
  TIME_TO_SEC(MAX(TIMEDIFF(NOW(), supervisor_time_data.time_waiting))) AS MTE,
  TIME_TO_SEC(MAX(TIMEDIFF(NOW(), supervisor_time_data.time_attending))) AS MTA,
  supervisor.tme_accumulated   AS TME_ACCUMULATED,
  supervisor.tma_accumulated   AS TMA_ACCUMULATED
FROM supervisor
  LEFT JOIN supervisor_time_data
    ON supervisor_time_data.id_service = supervisor.id_service
  LEFT JOIN agenda
    ON supervisor.id_service = agenda.id
  LEFT JOIN specialities
    ON agenda.idspeciality = specialities.id
WHERE supervisor.booked_or_sequential = 0
    AND supervisor.id_service IN(1,2,3)
GROUP BY supervisor.id_service
ORDER BY agenda.name ASC;