php - MySQL与InnoDB:如何避免使用COUNT？

因此，我有一个表，其中填充了约700K个条目以进行测试，这表明它在MySQL查询中很痛苦。

有问题的表：

CREATE TABLE `trades` (
 `tradeId` int(11) NOT NULL AUTO_INCREMENT,
 `userId` int(11) NOT NULL,
 `visible` int(11) NOT NULL DEFAULT '1',
 `sourceItem` int(11) NOT NULL,
 `sourceKeyTierId` int(11) DEFAULT NULL,
 `sourceKeyTypeId` int(11) DEFAULT NULL,
 `sourceKeyAmount` int(11) DEFAULT NULL,
 `sourceModId` int(11) DEFAULT NULL,
 `sourceModLevel` int(11) DEFAULT NULL,
 `destinationItem` int(11) NOT NULL,
 `destinationPlatinum` int(11) DEFAULT NULL,
 `destinationKeyTierId` int(11) DEFAULT NULL,
 `destinationKeyTypeId` int(11) DEFAULT NULL,
 `destinationKeyAmount` int(11) DEFAULT NULL,
 `destinationModId` int(11) DEFAULT NULL,
 `destinationModLevel` int(11) DEFAULT NULL,
 `added` datetime NOT NULL,
 PRIMARY KEY (`tradeId`),
 KEY `userId` (`userId`),
 KEY `sourceKeyTierId` (`sourceKeyTierId`),
 KEY `sourceKeyTypeId` (`sourceKeyTypeId`),
 KEY `sourceModId` (`sourceModId`),
 KEY `destinationKeyTierId` (`destinationKeyTierId`),
 KEY `destinationKeyTypeId` (`destinationKeyTypeId`),
 KEY `destinationModId` (`destinationModId`),
 CONSTRAINT `trades_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`),
 CONSTRAINT `trades_ibfk_2` FOREIGN KEY (`sourceKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_3` FOREIGN KEY (`sourceKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_4` FOREIGN KEY (`sourceModId`) REFERENCES `mods` (`modId`),
 CONSTRAINT `trades_ibfk_5` FOREIGN KEY (`destinationKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_6` FOREIGN KEY (`destinationKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_7` FOREIGN KEY (`destinationModId`) REFERENCES `mods` (`modId`)
) ENGINE=InnoDB AUTO_INCREMENT=732544 DEFAULT CHARSET=latin1

现在，当获取结果集时，我要计算结果的数量，以决定是否显示未找到结果的消息。

SELECT SUM(count) AS sum
FROM
(
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 1)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 2)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 3)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 1)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 2)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 3)
) AS derived

该查询正在运行，但是需要2.63秒，这很长。

如何优化呢？我以为自己几乎可以做的一切，除了一件事情：


由于sourceItem的可能值为(1, 2)，destinationItem的可能值为(1, 2, 3)，因此我可以制作另一个表，并通过TRIGGER ON INSERT写入包含值的表。


同样重要的是，查询由依赖于POST变量的PHP脚本创建，这意味着SELECT中的每个UNION ALL可能存在，也可能不存在。不幸的是，这个问题并不像返回整个表的最大值那样容易。

也欢迎所有其他建议。

更新：显然在查询的实际构造上存在一些困惑，如下所示：


您有2个sourceItem复选框，分别与1和2
您有3个destinationItem复选框，分别与1，2和3


用户可以按照他想要的任何方式检查它们。

更新2：似乎即使使用索引，我的原始查询也不会削减它，有人会这么想考虑一种完全不同的设置，该设置基本上消除了对COUNT或SUM或任何此类需求的需求吗？

更新3：我忘记了我问题中非常重要的部分，如下：


sourceItem = 1可能与（sourceKeyTierId = ? AND sourceKeyTypeId =？）相关联
sourceItem = 2可能与（sourceModId = ?）相关联
destinationItem = 2可能与（destinationKeyTierId = ? AND destinationKeyTypeId =？）相关联
destinationItem = 3可能与（destinationModId = ?）相关联


您仍然可以在带有复选框的示例中看到它，但是选中某些复选框也可以选择输入另一个数字（在实际情况下，这是一个选择下拉列表），而无需选择。

我的第一个未优化查询的更新示例是也可能发生以下查询，但这是最大示例：

SELECT SUM(count) AS sum
FROM
(
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 1 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 2 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ? AND t.destinationKeyTierId = ? AND t.destinationKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 3 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ? AND t.destinationModId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 1 AND t.sourceModId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 2 AND t.sourceModId = ? AND t.destinationKeyTierId = ? AND t.destinationKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 3 AND t.sourceModId = ? AND t.destinationModId = ?)
) AS derived

最佳答案

将您的条件放在sum()中。像这样你得到单数

SELECT SUM(sourceItem = 1 AND destinationItem = 1) AS count1,
       SUM(sourceItem = 1 AND destinationItem = 2) AS count2
FROM trades

要获取完整的条件计数，请执行

SELECT SUM(case when sourceItem > 0 and destinationItem > 0 then 2
                when sourceItem > 0 or destinationItem > 0 then 1
                else 0
           end) AS complete_sum
FROM trades

要么

SELECT SUM(sourceItem > 0) + sum(destinationItem > 0) AS complete_sum
FROM trades

关于php - MySQL与InnoDB:如何避免使用COUNT？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20924951/