Mysql子查询优化-count

Question

我在主查询中有这些子查询，用于获取一些事件:

SELECT [...],
(SELECT COUNT(*) FROM WEventUser WHERE WEventUser.eID=e.eID AND favorited=1) as numfavorited,
(SELECT COUNT(*) FROM WEventUser WHERE WEventUser.eID=e.eID AND subscribed=1) as numsubscribed,
(SELECT COUNT(*) FROM WEventUser WHERE eID=e.eID AND WEventUser.uID=2 AND favorited=1) as favorited,
(SELECT COUNT(*) FROM WEventUser WHERE eID=e.eID AND WEventUser.uID=2 AND subscribed=1) as subscribed,
[...] WHERE...etc.

WEventUser的结构非常简单

CREATE TABLE IF NOT EXISTS `WEventUser` (
  `eID` int(10) unsigned NOT NULL auto_increment,
  `uID` int(10) unsigned NOT NULL,
  `favorited` int(1) unsigned default '0',
  `subscribed` int(1) unsigned default '0',
  PRIMARY KEY  (`eID`,`uID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

这些子查询非常昂贵。您能帮我找到替代方案(例如单个连接)吗？

提前致谢!

编辑:我从主 WEvents 表中进行选择:

CREATE TABLE IF NOT EXISTS `wevents` (
`eID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `uID` int(10) unsigned DEFAULT NULL,
 `ecID` int(10) unsigned NOT NULL,
 `eName` varchar(64) NOT NULL,
 `eDescription` longtext,
 `eIsActive` varchar(1) NOT NULL DEFAULT '0',
 `eIsValidated` tinyint(4) NOT NULL DEFAULT '-1',
 `eDateAdded` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `eDateModified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`eID`,`ecID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Answer 1

您不应该使用子查询，在 COUNT 函数中计算值就足够了，例如-

SELECT [...],
COUNT(IF(wu.favorited = 1, 1, NULL)) as numfavorited,
COUNT(IF(wu.subscribed = 1, 1, NULL)) as numsubscribed,
COUNT(IF(wu.uID=2 AND wu.favorited=1, 1, NULL)) as favorited,
COUNT(IF(wu.uID=2 AND wu.favorited = 1, 1, NULL)) as subscribed,
[...]
FROM
  WEventUser wu
WHERE...etc.

如果您想将 WEventUser 与另一张表连接起来，您可以轻松使用此表。