DelphiFAQ Home Search:

count(distinct fieldname) very slow in mysql

 

commentsThis article has not been rated yet. After reading, feel free to leave comments and rate it.

Question:

I need to count how many different messages are in my table. The field is msg_id. I do have a (non-unique) index defined on this field, and it is also part of the primary key. The count is very slow. Any ideas how to make it faster? Using mysql 5.0

Answer:

For some reason, a nested select makes this much faster. See the resulting times below. A nested SELECT takes very consistently 2.1 seconds. The more straight forward single SELECT takes between 12 and 21 seconds.

mysql> select count(distinct msg_id) from (select * from out_msgs) as o;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (2.18 sec)

mysql> select count(distinct msg_id) from (select * from out_msgs) as o;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (2.11 sec)

mysql> select count(distinct msg_id) from out_msgs;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (14.73 sec)

mysql> select count(distinct msg_id) from out_msgs;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (1.77 sec)

mysql> select count(distinct msg_id) from out_msgs;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (21.48 sec)

mysql> select count(distinct msg_id) from out_msgs;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (12.72 sec)

mysql> select count(distinct msg_id) from (select * from out_msgs) as o;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (2.12 sec)

mysql> select count(distinct msg_id) from (select * from out_msgs) as o;
+------------------------+
| count(distinct msg_id) |
+------------------------+
|                 451286 |
+------------------------+
1 row in set (2.14 sec)

mysql>

Content-type: text/html

Comments:

2012-04-04, 07:18:28
anonymous from India  
It does not work when there are join in the sql query. Please see image.


Keywords:

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: