优化sql查询，即使对于小数据也太慢

Question

基本上我试图从每个url匹配的单词中获得总计数。我有这样的SQL查询：

select w.url, w.word, w.count, (
select sum(w2.count) 
from wordcounts w2 where w2.url = w.url and w2.word in ('search', 'more') 
) as totalcount 
from wordcounts w 
where w.word in ('search', 'more') 

我使用此查询得到这样的结果：

URL        | word | count | Total Count 

http://haacked.com/    | more | 61 | 62 
http://haacked.com/    | search | 1  | 62 
http://feeds.haacked.com/haacked | more | 58 | 59 
http://feeds.haacked.com/haacked | search | 1  | 59 
http://www.asp.net/privacy  | more | 7  | 13 
http://www.asp.net/privacy  | search | 6  | 13 

我原来的表结构

ID | URL | word | count 

但问题是，这小型查询需要花费太多时间。 7+秒以上在几千行上查询。我该如何优化这个查询？

我从另一个网站得到了这个语法，但它给错误。

select id, url, word, count, 
sum(count) over(partition by url) as count_sum 
from wordcounts where word in ('search', 'more') order by url 

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by url) as count_sum 
from wordcounts where word in ('search', 'more')' at line 2 
Line 1, column 1 

Execution finished after 0 s, 1 error(s) occurred. 

Answer 1

预汇总：

select w.url, w.word, w.`count`, w3.totalcount 
from wordcounts w 
join (
    select w2.url, sum(w2.`count`) totalcount 
    from wordcounts w2 
    where w2.word in ('search', 'more') 
    group by w2.url) w3 on w3.url = w.url 
where w.word in ('search', 'more') 

Answer 2

使用JOIN，而不是一个子查询：

select w.url, w.word, w.count, sum(w2.count) as totalcount 
from wordcounts w 
left join wordcounts w2 
    on w2.url = w.url and w2.word in ('search', 'more') 
where w.word in ('search', 'more') 
group by w.url, w.word, w.count 

Answer 3

您最初的查询运行缓慢在MySQL，因为MySQL是执行子查询的每一行结果集。您可以通过做一次汇总，并加入该结果解决这个问题：

select w.url, w.word, w.count, wsum.sumcount 
from wordcoutns w join 
    (select w.url, w.word, SUM(w.count) as sumcount 
     from wordcounts w 
     where w.word in ('search', 'more') 
     group by w.url, w.word 
    ) wsum 
    on wsum.url = w.url and wsum.word = w.word 
where w.word in ('search', 'more') 

其他数据库支持的一类叫做窗口功能，使这更容易的功能。 MySQL不支持这些。