MySQL查询速度慢(连接/计数)

Question

我有一个疑问：

 SELECT `assemblies`.`id`,
   `assemblies`.`type`,
   `assemblies`.`champion`,
   `assemblies`.`name`,
   `assemblies`.`author`,
   `assemblies`.`githublastmod`,
   ( assemblies.forum IS NOT NULL ) AS forumExists,
   Count(votes.id)                  AS votesCount,
   Count(install_clicks.id)         AS installCount,
   Count(github_clicks.id)          AS githubCount,
   Count(forum_clicks.id)           AS forumCount
FROM   `assemblies`
   INNER JOIN `votes`
           ON `votes`.`assembly` = `assemblies`.`id`
   INNER JOIN `install_clicks`
           ON `install_clicks`.`assembly` = `assemblies`.`id`
   INNER JOIN `github_clicks`
           ON `github_clicks`.`assembly` = `assemblies`.`id`
   INNER JOIN `forum_clicks`
           ON `forum_clicks`.`assembly` = `assemblies`.`id`
WHERE  `assemblies`.`type` = 'utility'
   AND Unix_timestamp(Date(assemblies.githublastmod)) > '1419536536'
GROUP  BY `assemblies`.`id`
ORDER  BY `votescount` DESC,
      `githublastmod` DESC  

由于某些原因，这个查询非常慢，我使用的是数据库引擎MyISAM。我希望有人能在这里帮助我

解释命令：

​

Answer 1

我相信，在这种情况下，对计数进行子查询将使其运行得更快(并且值将是正确的)。

原始查询的问题是中间行数激增:对于每个“程序集”，有n1投票、n2安装等，这导致了N1*N2*.每个程序集的行。

SELECT  `assemblies`.`id`, `assemblies`.`type`, `assemblies`.`champion`,
        `assemblies`.`name`, `assemblies`.`author`, `assemblies`.`githublastmod`,
        ( assemblies.forum IS NOT NULL ) AS forumExists, 
      ( SELECT  Count(*)
            FROM  votes
            WHERE  `assembly` = `assemblies`.`id` 
      ) AS votesCount, 
      ( SELECT  Count(*)
            FROM  install_clicks
            WHERE  `assembly` = `assemblies`.`id` 
      ) AS installCount, 
      ( SELECT  Count(*)
            FROM  github_clicks
            WHERE  `assembly` = `assemblies`.`id` 
      ) AS githubCount, 
      ( SELECT  Count(*)
            FROM  forum_clicks.id
            WHERE  `assembly` = `assemblies`.`id` 
      ) AS forumCount
    FROM  `assemblies`
    WHERE  `assemblies`.`type` = 'utility'
      AND  Unix_timestamp(Date(assemblies.githublastmod)) > '1419536536'
    ORDER BY  `votescount` DESC, `githublastmod` DESC 

每个辅助表都需要一个以assembly开头的索引。

Answer 2

您的问题应该使用正确的索引来解决：

CREATE INDEX index_name_1 ON `votes`(`assembly`);
CREATE INDEX index_name_2 ON `install_clicks`(`assembly`);
CREATE INDEX index_name_3 ON `github_clicks`(`assembly`);
CREATE INDEX index_name_4 ON `forum_clicks`(`assembly`);

在创建这些索引之后再试一次查询，它应该会更快。