0

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?

SELECT  `email`
FROM  `table_of_downloads` 
GROUP BY  `email` 
HAVING COUNT( * ) >100

Here is the EXPLAIN results as requested:

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
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
SAVAFA
  • 818
  • 8
  • 23

2 Answers2

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I added the `email` column as index but there was no noticeable difference in query time, still more than 11 seconds. – SAVAFA Aug 18 '13 at 02:05
  • Can you post the EXPLAIN output? – Barmar Aug 18 '13 at 02:07
  • I just added that in the question text. – SAVAFA Aug 18 '13 at 02:14
  • I don't understand why it's not using the index you added. I tried it on one of my large tables (7.2M rows) and it took 1.5 seconds. Post `show create table table_of_downloads`. – Barmar Aug 18 '13 at 02:18
  • Is there any specific part that you want me to put in here? It's a kind of long and I rather not to publish all names and details of the table (I don't know why, sort of phobia). – SAVAFA Aug 18 '13 at 02:35
  • I just want to see the `KEY` lines that say that there's an index on `email`. – Barmar Aug 18 '13 at 02:40
  • Here it is: PRIMARY KEY (`Request_id`), UNIQUE KEY `Uniqueness` (`RequestDate`,`RqstdFileID`,`email`(50)), KEY `Requested` (`Requested`(50),`RequestType`,`UserName`), KEY `email` (`email`(128)) ) ENGINE=InnoDB AUTO_INCREMENT=1656559 DEFAULT CHARSET=latin1 – SAVAFA Aug 18 '13 at 02:50
  • I don't know how to put that line in a formatted way. Thanks in advance. – SAVAFA Aug 18 '13 at 02:53
  • You can only format in questions, not comments. Is 128 the whole size of the `email` column? You need a key that covers the entire column. – Barmar Aug 18 '13 at 02:59
  • Yes, the maximum length of the `email` in the table is 125 and therefor I put 128 in the index, besides the type for `email` is `tinytext`. – SAVAFA Aug 18 '13 at 03:14
  • Try creating the index without specifying a size -- I suspect it's not being used because it doesn't match the size of the column. – Barmar Aug 18 '13 at 03:18
  • As you may know, when the type is `tinytext` you need to specify a length, and that's why I assigned 128 which is bigger than the largest value. – SAVAFA Aug 18 '13 at 03:22
  • 1
    It looks like an index on a text type can't be used for grouping. – Barmar Aug 18 '13 at 03:38
  • WOW! Impressive. I just changed the type from `tinytext` to `varchar(128)` and the query time went down to 0.03 seconds :) – SAVAFA Aug 18 '13 at 05:43
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.

SAVAFA
  • 818
  • 8
  • 23