我有一个表USERDATA
,我在其中添加了几个新列。所有这些都不是 NULL,默认为 0 或 1。但是,这样做后我的网站变得很慢。我的意思是真的很慢。这是因为我网站上的这个查询脚本:
$num_rows = $db->doQuery('SELECT TOP 50 IDNum, IDName, Nation, (SELECT SUM(Loyalty) FROM USERDATA WHERE USERDATA.Knights = KNIGHTS.IDNum AND USERDATA.Authority = 1) as ClanLoyalty, (SELECT SUM(MannerPoint) FROM USERDATA WHERE USERDATA.Knights = KNIGHTS.IDNum AND USERDATA.Authority = 1) as ClanManner FROM KNIGHTS WHERE KNIGHTS.IDNum NOT IN (1,15001) ORDER BY ClanLoyalty DESC, IDName ASC');
它以前工作得很好,但是在添加了上面此查询中根本没有包含的新列之后..它仍然减慢了网站请求TOP 50的速度..基本上SUM
查询正在这样做..为什么这些新列会产生这样的问题?
USERDATA
只有 32k 行,以前效果很好。我没有指定 contain,这可能是问题吗?
我使用 MSSQL Server 2005。
像这样更改查询。
SELECT TOP 50 IDNum,
IDName,
Nation,
Sum(Loyalty) ClanLoyalty,
Sum(MannerPoint) ClanManner
FROM KNIGHTS
LEFT JOIN USERDATA
ON USERDATA.Knights = KNIGHTS.IDNum
AND USERDATA.Authority = 1
WHERE KNIGHTS.IDNum NOT IN ( 1, 15001 )
Group by IDNum,IDName,Nation
ORDER BY ClanLoyalty DESC,
IDName ASC