Description:
According to discussion with Igor this query uses filesort on group by for its
execution however it is missing in explain, furthermore the fact is missing what temporary
table is used for count(distinct)
mysql> explain select i,count(distinct(i)) from fs group by j order by null;
+-------+------+---------------+------+---------+------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------+
| fs | ALL | NULL | NULL | NULL | NULL | 6 | |
+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE fs (
i int(11) default NULL,
j int(11) default NULL
) TYPE=MyISAM;
--
-- Dumping data for table 'fs'
--
INSERT INTO fs VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
explain select i,count(distinct(i)) from fs group by j order by null;
