MYSQL count(DISTINCT an AUTO_INCREMENT column)-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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