MYSQL介质查询优化与子查询

Question

我有这个查询，返回以下内容:

• 区域名称
• 区域ID
• 该地区的排名(位置)，基于投票总数
• 该区域不同用户的Nb
• 该区域的不同照片Nb

查询耗时约~7.5s完成…我需要一些建议来优化我的查询。

            select
            WrappedQuery.*,
            regions.name as region_name,
            regions.id as region_id,
            count(distinct users.id) as nb_users,
            count(distinct photos.id) as nb_photos
            from (
                select
                  @rownum := @rownum +1 as rank,
                  prequery.region_id,
                  prequery.VoteCount
                from
                  ( select @rownum := 0 ) sqlvars,
                  ( select region_id, count(id) VoteCount
                      from votes
                      where theme_id = '{$currentTheme}'
                      group by region_id
                      order by count(id) desc ) prequery
              ) WrappedQuery, regions, users, photos
              WHERE regions.id = WrappedQuery.region_id
              AND users.region_id = WrappedQuery.region_id
              AND photos.region_id = WrappedQuery.region_id
              GROUP BY WrappedQuery.region_id
              ORDER BY WrappedQuery.rank ASC
              LIMIT 0, 1

Answer 1

您的查询对于您想要实现的目标有太多的开销。我已经为你重写了…

select 
/*you don't need that
@rownum := @rownum +1 as rank, 
*/
regions.name as region_name,
regions.id as region_id,
count(distinct users.id) as nb_users,
count(distinct photos.id) as nb_photos,
count(votes.id) as VoteCount
from votes
INNER JOIN regions ON votes.region_id = regions.id
INNER JOIN users ON users.region_id = regions.id
INNER JOIN photos ON photos.region_id = regions.id
/*you don't need that
, ( select @rownum := 0 ) sqlvars
*/
where theme_id = '{$currentTheme}'
group by regions.id
order by VoteCount DESC
LIMIT 1

我注释掉了有秩的部分，因为无论如何你只想要1行。

如果它仍然太慢，你必须发布EXPLAIN SELECT .../*the query from above*/的结果，这样我们就可以看到是否使用了索引。同时发布表创建脚本(使用SHOW CREATE TABLE tableName)。要么这样做，要么尝试自己创建缺失的索引。

更新:

再次重写你的查询，这样可能会更快:

select
WrappedQuery.*,
regions.name as region_name,
regions.id as region_id,
count(distinct users.id) as nb_users,
count(distinct photos.id) as nb_photos
from (
       select region_id, count(id) VoteCount
          from votes
          where theme_id = '{$currentTheme}'
          group by region_id
          ORDER BY VoteCount DESC
          LIMIT 1
  ) WrappedQuery, regions, users, photos
  WHERE regions.id = WrappedQuery.region_id
  AND users.region_id = WrappedQuery.region_id
  AND photos.region_id = WrappedQuery.region_id
  GROUP BY WrappedQuery.region_id