Description:
this merges:
explain select * from ( select * from t1 ) x limit 1;
this does not:
explain select * from ( select * from t1 group by a ) x limit 1;
this actually ends up scanning whole table, even if outer select would terminate inner select after just one row read:
mysql> explain select * from ( select * from t1 group by a ) x limit 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3328256 | 100.00 | NULL |
| 2 | DERIVED | t1 | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 3328256 | 100.00 | NULL |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
How to repeat:
this repro is simplification of many different problems we observe in our environment.
please be nice and don't laugh at it.
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
)
select * from ( select * from t1 group by a ) x limit 1;
Suggested fix:
Whatever Evgeny Potemkin showed as proof of concept few years ago.