大表查询优化(mysql)
大表查询优化(mysql)
提问于 2011-05-14 13:33:10
这个表包含1.2ill的结果，我不能编辑它，因为有一些应用程序我也没有访问它的源代码。我想添加一个数量字段，但我不能。
下面是我正在使用的查询：
SELECT SUM(assets.hourlyEarnings) as earnings,
assets_inventory.uid
FROM (assets)
JOIN assets_inventory ON assets.id = assets_inventory.assetID
WHERE assets_inventory.uid IN (SELECT users.uid
FROM users
WHERE users.assetTime < 1305350756)
GROUP BY uid有许多重复的记录。
下面是表格：
CREATE TABLE IF NOT EXISTS assets_inventory (
id int(11) NOT NULL AUTO_INCREMENT,
uid bigint(20) NOT NULL,
assetID int(11) NOT NULL,
PRIMARY KEY (id),
KEY uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1231992 ;平均来说，我花了6-7秒来获取结果，任何加速结果的建议都将不胜感激！
回答 1
Stack Overflow用户
回答已采纳
发布于 2011-05-14 13:55:38
如果您想要所有uid值的列表，无论是否有关联的收益：
SELECT DISTINCT
ai.uid,
COALESCE(x.earnings, 0) AS earnings
FROM ASSETS_INVENTORY ai
LEFT JOIN (SELECT t.id,
SUM(t.hourlyearnings) AS earnings
FROM ASSETS t
GROUP BY t.id) x ON x.id = ai.assetid
WHERE EXISTS (SELECT NULL
FROM USERS u
WHERE u.uid = ai.uid
AND u.assettime < 1305350756)否则：
SELECT ai.uid,
SUM(a.hourlyearnings) AS earnings
FROM ASSETS_INVENTORY ai
JOIN ASSETS a ON a.id = ai.assetid
WHERE EXISTS (SELECT NULL
FROM USERS u
WHERE u.uid = ai.uid
AND u.assettime < 1305350756)
GROUP BY ai.uid...or：
SELECT ai.uid,
SUM(a.hourlyearnings) AS earnings
FROM ASSETS_INVENTORY ai
JOIN ASSETS a ON a.id = ai.assetid
JOIN USERS u ON u.uid = ai.uid
AND u.assettime < 1305350756
GROUP BY ai.uid页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6000015
复制相关文章
相似问题
腾讯云开发者
