Bug #79040 please merge more temptables
Submitted: 30 Oct 2015 21:49 Modified: 5 Nov 2015 14:07
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[30 Oct 2015 21:49] Domas Mituzas
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.
[5 Nov 2015 14:07] Sinisa Milivojevic
Hi Mr. Mituzas,

I have studied your report carefully and I have no doubts that this is fully justified feature request, that should get a proper priority.

Verified.
[31 May 2017 8:42] Vladislav Rastrusny
Has it ever got a proper priority for the last 2 years?