MYSQL子查询计数慢

Question

我有以下查询，可以提取我需要的信息，但使用速度太慢。它基本上是对两个表进行选择查询，拉入各个列，并且还包括来自另一个表的计数。我希望能得到一些帮助，因为这几天一直在努力。我在 tblDriver 状态列上有一个索引。

SELECT date_format(`tblAvailability`.`adate`,'%e %b %Y') AS `adate`,
  date_format(`tblAvailability`.`adate`,'%a') AS `aday`, `tblVenue`.`name` AS `name`, 
  tblAvailability.cars, `tblAvailability`.`adate` AS `bdate`,
`tblAvailability`.`totalslots` AS `totslots`,
(SELECT count(*) FROM `tblDriver` WHERE ((`tblDriver`.`status` <> 'refunded') and (`tblDriver`.`status` <> 'rejected') and (`tblDriver`.`status` <> 'rebook') and (`tblDriver`.`status` <> 'rebook-cust') and (`tblDriver`.`eventid` = `tblAvailability`.`id`))) AS `bslots` 
    FROM (`tblAvailability` join `tblVenue` on((`tblAvailability`.`idvenue` = `tblVenue`.`id`))) 
    WHERE ((`tblAvailability`.`adate` > now()) 
    AND (`tblAvailability`.`status` <> 'inactive') and (`tblAvailability`.`status` <> 'removed')) 
    GROUP BY `tblAvailability`.`adate`,`tblVenue`.`name` 
    ORDER BY `tblAvailability`.`adate`,`tblVenue`.`name`

Answer 1

这是您的查询，经过一些清理:

SELECT date_format(a.`adate`,'%e %b %Y') AS adate, date_format(a.adate, '%a') AS aday, 
       v.name, a.cars, a.adate AS bdate, a.`totalslots` AS `totslots`,
       (SELECT count(*)
        FROM `tblDriver` d
        WHERE d.status not in ('refunded', 'rejected', 'rebook', 'rebook-cust') and
              d.eventid = a.id
       ) AS bslots 
FROM tblAvailability a join
     tblVenue v
     on a.idvenue = v.id
WHERE a.adate > now() AND
      a.status not in ('inactive', 'removed')
GROUP BY a.adate, v.name
ORDER BY a.adate, v.name;

现在它已经具有可读性了，可以提出建议了。 driver(status) 上的索引可能没有帮助。尝试这些索引:

tblDriver(eventid, status)
tblAvailability(adate, status, idvenue)
tblVenue(id)

最后一个可能不需要，因为 venue.id 可能被声明为主键。