count(distinct fieldname) very slow in mysql

Databases
InterBase (28)
MS-SQL (5)
mysql (33)
Oracle (1)

count(distinct fieldname) very slow in mysql

This 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>

Comments:


	Search: