Question 113134 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL count(DISTINCT an AUTO_INCREMENT column)

The following result is quite surprising.

MySQL [emicall_cc_man]> SELECT count(DISTINCT `id`) from call_record;
+----------------------+
| count(DISTINCT `id`) |
+----------------------+
|              2197537 |
+----------------------+
1 row in set (1.32 sec)

MySQL [emicall_cc_man]> SELECT count(`id`) from call_record;
+-------------+
| count(`id`) |
+-------------+
|     2197537 |
+-------------+
1 row in set (0.27 sec)

The id column is defined as AUTO_INCREMENT, and since it is already unique why did SELECT count(DISTINCT id) take 5 times longer than SELECT count(id) ? It seems mysql (5.7) did not take that into account at all.

Is it a bug or some other explanation ?

CREATE TABLE `call_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
  `sid` bigint(20) unsigned NOT NULL DEFAULT '0' ,
  ...
  PRIMARY KEY (`id`,`seid`),
  ...
) ENGINE=InnoDB
Tags:

2

Answers


  1. 0

    DISTINCT() it’s an extra operation that consumes CPU cycles. Why would someone use COUNT(DISTINCT) on a unique column instead of COUNT?

    Login or Signup to reply.
  2. 0

    The distinct clause implies that the result is obtanied using a temp table managed
    by then db engine that starting from the original is used for group by the values before select the finale result..

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search