优化慢查询mysql

Question

我使用下面的查询来选择一个用户列表，但是查询速度非常慢。有人能给我解释一下如何优化这个查询吗？

提前谢谢。

问候弗雷德。

    SELECT 

    crmUser.userID, 
    crmUser.userFirstName, 
    crmUser.userLastName, 
    crmUser.userSekse, 
    TIMESTAMPDIFF(YEAR, crmUser.userBirthday,NOW()) AS age, 
    crmUser.userTelephone, 
    crmUser.userEmail,                          
    crmUser.userCity, 
    crmUser.userPlaceOfBirth, 
    content_City.province, 

    MAX(DATE_FORMAT(crmConnect.connectStamp, '%Y-%m-%d')) AS laatstGesolliciteerd,
    TIMESTAMPDIFF(WEEK, MAX(crmConnect.connectStamp),NOW()) AS laatsteActiviteit,
    COUNT(DISTINCT crmConnect.connectParent) AS Jobs,                               
    SUM(IF(crmConnect.connectExtra = 'interest', 1, 0)) AS Interest,
    SUM(IF(crmConnect.connectExtra = 'select', 1, 0)) AS Prospect,
    SUM(IF(crmConnect.connectExtra = 'winner', 1, 0)) AS Winner

FROM crmUser 
LEFT JOIN content_City ON (content_City.cityName = crmUser.userCity)
LEFT JOIN crmConnect ON (crmConnect.connectChild = crmUser.userID) 
LEFT JOIN crmJob ON (crmJob.jobID = crmConnect.connectParent) 

WHERE 

    crmUser.userID NOT IN (111, 222, 333, 444) AND
    crmUser.userActive = 1 AND 
    crmUser.userExtra = 0 AND

    crmConnect.connectType = 'user' AND 
    crmConnect.connectStamp != '0000-00-00 00:00:00' 

GROUP BY userID 

ORDER BY userID ASC

LIMIT 3000

Answer 1

您可以消除作业的join (据我所知)。您还可以简化一些逻辑，尽管这不会对性能产生太大影响。使用此查询：

SELECT u.userID, u.userFirstName, u.userLastName, u.userSekse, 
       TIMESTAMPDIFF(YEAR, u.userBirthday, NOW()) AS age, 
       u.userTelephone, u.userEmail, u.userCity, u.userPlaceOfBirth, 
       cc.province, 
       MAX(c.connectStamp) AS laatstGesolliciteerd,
       TIMESTAMPDIFF(WEEK, MAX(c.connectStamp), NOW()) AS laatsteActiviteit,
       COUNT(DISTINCT c.connectParent) AS Jobs,                               
       SUM(c.connectExtra = 'interest') AS Interest,
       SUM(c.connectExtra = 'select') AS Prospect,
       SUM(c.connectExtra = 'winner') AS Winner
FROM crmUser u LEFT JOIN
     content_City cc
     ON cc.cityName = u.userCity LEFT JOIN
     crmConnect c
     ON c.connectChild = u.userID
WHERE u.userID NOT IN (111, 222, 333, 444) AND
      u.userActive = 1 AND 
      u.userExtra = 0 AND
      c.connectType = 'user' AND 
      c.connectStamp <> '0000-00-00 00:00:00' 
GROUP BY u.userID ;

您需要索引。我建议使用crmuser(userActive, userExtra, UserID)、content_City(cityName, province)和crmConnect(connectChild, connectType, connectStamp, connectParent, connectExtra)。