I have a table with 1.5M+ rows for recording downloads from a website which has email address of the one who has downloaded something. I want to find those who have downloaded more than 100 times. This is what I have tested but the query-time is more than 11 seconds when I test it on the server! Do you know any faster way?
我有一个1.5M +行的表用于记录来自网站的下载,该网站的电子邮件地址是已下载的东西。我想找到那些下载次数超过100次的人。这是我测试过的,但是当我在服务器上测试时查询时间超过11秒!你知道更快的方式吗?
SELECT `email`
FROM `table_of_downloads`
GROUP BY `email`
HAVING COUNT( * ) >100
Here is the EXPLAIN results as requested:
这是请求的EXPLAIN结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table_of_downloads ALL NULL NULL NULL NULL 1656546 Using temporary; Using filesort
2 个解决方案
#1
0
You need to have an index on the email column. Otherwise, the query has to scan the entire table to count the number of rows for each email. There's no way to make it faster other than with an index.
您需要在电子邮件列上有一个索引。否则,查询必须扫描整个表以计算每封电子邮件的行数。除了索引之外,没有办法让它更快。
#2
0
For others to know, I just changed the type from tinytext to varchar(128) and the query time went down to 0.03 seconds.
对于其他人来说,我只是将类型从tinytext更改为varchar(128),查询时间下降到0.03秒。
#1
0
You need to have an index on the email column. Otherwise, the query has to scan the entire table to count the number of rows for each email. There's no way to make it faster other than with an index.
您需要在电子邮件列上有一个索引。否则,查询必须扫描整个表以计算每封电子邮件的行数。除了索引之外,没有办法让它更快。
#2
0
For others to know, I just changed the type from tinytext to varchar(128) and the query time went down to 0.03 seconds.
对于其他人来说,我只是将类型从tinytext更改为varchar(128),查询时间下降到0.03秒。