mysql - 使用多个表优化查询

Question

我有一个查询，其中的数据来自多个表。

SELECT iUserID,
       iUserID AS userID,
       (CASE vUSBG
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
        END) AS vUSBG,
       concat(vFirstname,' ',vLastname) AS Name,
       vEmail,
       eType,
       eStatus,
       tAddedDate,
       eExpert,
       eAdmin,

  (SELECT count(iUserID) AS total
   FROM tbl_friend
   WHERE iUserID = tbl_user.iUserID) AS count_f,

  (SELECT COUNT(*)
   FROM bar_followers
   WHERE bar_followers.iUserID = tbl_user.iUserID) AS bar_follows,

  (SELECT COUNT(b.iBrandID)
   FROM tbl_company_follow,
        tbl_brand b
   WHERE tbl_company_follow.iUserID = tbl_user.iUserID
     AND b.iCompanyID = tbl_company_follow.iCompanyID) AS brand_follows,

  (SELECT sum(points) AS totalpoints
   FROM tbl_points,
        tbl_post p
   WHERE iUserID = tbl_user.iUserID
     AND p.iPostID = tbl_points.post_id) AS countPoints
FROM tbl_user

此查询花费了 8.3595 秒

我怎样才能最大限度地缩短时间？

Answer 1

除了缺少明确的 join 语法外，您的查询是合理的。

检查以确保您具有以下索引:

• tbl_friend(iUserId)
• bar_followers(iUserID)
• tbl_company_follow(iUserID, iCompanyID)
• tbl_brand(iCompanyID)
• tbl_post(iUserID, iPostID)
• tbl_points(post_id, points)

但是，根据您的数据大小，8-9 秒可能是合理的。