MySQL JOIN 时间减少

Question

此查询需要一分钟才能完成:

SELECT keyword, count(*) as 'Number of Occurences'
    FROM movie_keyword
    JOIN
    keyword
    ON keyword.`id` = movie_keyword.`keyword_id`
    GROUP BY keyword
    ORDER BY count(*) DESC
    LIMIT 5

每个关键字都有一个与之关联的 ID(keyword_id 列)。该 ID 用于从关键字表中查找实际关键字。

movie_keyword 有 280 万行

关键字有 127,000

然而，仅返回最常用的 keyword_id 只需 1 秒:

SELECT keyword_id, count(*)
    FROM movie_keyword
    GROUP BY keyword_id
    ORDER BY count(*) DESC
    LIMIT 5

有没有更有效的方法？

带 EXPLAIN 的输出:

1   SIMPLE  keyword ALL PRIMARY NULL    NULL    NULL    125405  Using temporary; Using filesort
1   SIMPLE  movie_keyword   ref idx_keywordid   idx_keywordid   4   imdb.keyword.id 28  Using index

结构:

CREATE TABLE `movie_keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mid` (`movie_id`),
  KEY `idx_keywordid` (`keyword_id`),
  KEY `keyword_ix` (`keyword_id`),
  CONSTRAINT `movie_keyword_keyword_id_exists` FOREIGN KEY (`keyword_id`) REFERENCES `keyword` (`id`),
  CONSTRAINT `movie_keyword_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4256379 DEFAULT CHARSET=latin1;

CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` text NOT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_keyword` (`keyword`(5)),
  KEY `idx_pcode` (`phonetic_code`),
  KEY `keyword_ix` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127044 DEFAULT CHARSET=latin1;

Answer 1

未经测试，但在我看来应该可以工作并且速度明显更快，但不太确定是否允许在 mysql 的子查询中使用限制，但还有其他解决方法。

SELECT keyword, count(*) as 'Number of Occurences'
    FROM movie_keyword
    JOIN
    keyword
    ON keyword.`id` = movie_keyword.`keyword_id`
    WHERE movie_keyword.keyword_id IN (
        SELECT keyword_id
        FROM movie_keyword
        GROUP BY keyword
        ORDER BY count(*) DESC    
        LIMIT 5
    )
    GROUP BY keyword
    ORDER BY count(*) DESC;

这应该会更快，因为您不会将 movie_keyword 中的所有 280 万个条目都与关键字连接起来，而只是连接实际匹配的条目，我猜这要少得多。

编辑 因为 mysql 不支持你必须运行的子查询内的限制

SELECT keyword_id
FROM movie_keyword
GROUP BY keyword
ORDER BY count(*) DESC    
LIMIT 5;

首先和获取结果后运行第二个查询

SELECT keyword, count(*) as 'Number of Occurences'
    FROM movie_keyword
    JOIN
    keyword
    ON keyword.`id` = movie_keyword.`keyword_id`
    WHERE movie_keyword.keyword_id IN (RESULTS_FROM_FIRST_QUERY_SEPARATED_BY_COMMAS)
    GROUP BY keyword
    ORDER BY count(*) DESC;

以编程方式将 RESULTS_FROM_FIRST_QUERY_SEPARATED_BY_COMMAS 替换为您使用的任何语言的正确值