Re: Real weird slow down with select count(distinct x) and high max_heap_table_size

[Ben Li]

got NO clue

mysql> set max_heap_table_size=1048576*64;
Query OK, 0 rows affected (0.00 sec)

mysql> select SQL_NO_CACHE count(distinct a) from dctest order by '';
+-------------------+
| count(distinct a) |
+-------------------+
| 5755225 |
+-------------------+
1 row in set (9.37 sec)

mysql> select SQL_NO_CACHE count(distinct a) from dctest order by ''\G
*************************** 1. row ***************************
count(distinct a): 5755225
1 row in set (9.32 sec)

mysql> set max_heap_table_size=1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> select SQL_NO_CACHE count(distinct a) from dctest order by ''\G
*************************** 1. row ***************************
count(distinct a): 5755225
1 row in set (4.72 sec)