优化慢查询mysql
优化慢查询mysql
提问于 2015-07-08 18:50:45
我使用下面的查询来选择一个用户列表，但是查询速度非常慢。有人能给我解释一下如何优化这个查询吗？
提前谢谢。
问候弗雷德。
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回答 1
Stack Overflow用户
发布于 2015-07-08 19:00:50
您可以消除作业的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)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31290486
复制相关文章
相似问题
腾讯云开发者
