大表查询优化(mysql)
问题描述:
该表包含1.2mill结果,我无法编辑它,因为有些应用程序我没有源代码来访问它。我想添加一个数量字段,但我不能。大表查询优化(mysql)
这里是我使用的查询:
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秒拉的结果,任何建议加快这将不胜感激!
答
如果你希望所有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
...或:
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
哎呀,让我试试其他2 – 2011-05-14 06:01:30
@Matt Dunbar:你真的需要使用BIGINT吗?数据类型的字节数越少(当前为8,INT为4),查询就会越快。 – 2011-05-14 06:09:39
不幸的是,它基于Facebook用户ID,它比无符号整数大。 – 2011-05-14 06:14:31