mysql - 如何优化该查询？

我的投票系统是这样设计的：

CREATE TABLE `vote` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `weight` int(11) NOT NULL,
  `submited_date` datetime NOT NULL,
  `resource_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2963832 DEFAULT CHARSET=latin1;

CREATE TABLE `article_preselection_vote` (
  `id` int(11) NOT NULL,
  `article_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_9B145DEA62922701` (`article_id`),
  KEY `IDX_9B145DEAA76ED395` (`user_id`),
  CONSTRAINT `article_preselection_vote_ibfk_4` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
  CONSTRAINT `article_preselection_vote_ibfk_5` FOREIGN KEY (`id`) REFERENCES `vote` (`id`) ON DELETE CASCADE,
  CONSTRAINT `article_preselection_vote_ibfk_6` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

v.weight可以是+1或-1，我需要，给定一堆文章ID，以获得每个文章ID的每一个正投票（+1）和负投票（-1）的总和。
那么我的结果应该是

article_id | vote_up | vote_down
-----------|---------|----------
    1      |    36   |     20
-----------|---------|----------
    68     |    12   |     56
-----------|---------|----------
    25     |    90   |     12
-----------|---------|----------

我可以通过下面的请求得到这个结果，但它相当重，而且在2000000张选票上速度很慢。

SELECT apv.article_id, COALESCE(SUM(up),0) as up, COALESCE(SUM(down),0) as down
FROM article_preselection_vote apv
LEFT JOIN(
    SELECT id, weight up FROM vote WHERE weight > 0 AND vote.resource_type = 'article') v1 ON apv.id = v1.id
LEFT JOIN(
    SELECT id, weight down FROM vote WHERE weight < 0 AND vote.resource_type = 'article') v2 ON apv.id = v2.id
WHERE apv.article_id IN (11702,11703,11704,11632,11652,11658)
GROUP BY apv.article_id

有什么想法吗？
提前谢谢。

最佳答案

您可以尝试单个联接：

SELECT
    apv.article_id,
    SUM(COALESCE(weight, 0) > 0) AS up,
    SUM(COALESCE(weight, 0) < 0) AS down
FROM article_preselection_vote apv
LEFT JOIN vote
    ON apv.id = vote.id
    AND vote.resource_type = 'article'
WHERE apv.article_id IN (11702, 11703, 11704, 11632, 11652, 11658)
GROUP BY apv.article_id

如果您需要经常计算这个值，那么可能需要对数据库进行非规范化并存储结果的缓存副本。

关于mysql - 如何优化该查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7896590/