DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

count(distinct fieldname) very slow in mysql

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>
 

Generated 4:00:39 on Mar 26, 2017