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

[Rick James]

Was dctest MyISAM or InnoDB? On my machine, the procedure ran 200x faster with MyISAM than with InnoDB! (I had autocommit on, and innodb_flush_log_at_trx_commit = 1.)

Two things would be helpful:
EXPLAIN SELECT ...
SHOW TABLE STATUS LIKE dctest;
The former may give a clue of whether it is using the 'heap', that is, using MEMORY table for intermediate processing. (It does not seem to give a clue.)
The latter will give some clue whether max_heap_table_size is bigger or smaller than the estimated temp table size.

The suggested ORDER BY should be irrelevant.

DISTINCT can be done one of two ways -- I can't seem to deduce which way it is doing it; but the settings seem to cause the difference.
* Keep a hash of all the values of `a`.
* Create a tmp table, sort it, then dedup.