CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6), (5,1), (5,2), (5,3), (5,4), (5,5); EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index a a 10 NULL X 100.00 Using index Warnings: Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `max(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` FLUSH STATUS; SELECT max(b), a FROM t1 GROUP BY a; max(b) a 5 1 3 2 1 3 6 4 5 5 SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 1 Handler_read_next 20 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index a a 10 NULL X 100.00 Using index Warnings: Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `max(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` FLUSH STATUS; CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 30 Handler_read_next 20 FLUSH STATUS; SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; max(b) a 5 1 3 2 1 3 6 4 5 5 SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 1 Handler_read_next 20 FLUSH STATUS; (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); max(b) a 5 1 3 2 1 3 6 4 5 5 SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 24 Handler_read_next 0 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range a a 5 NULL X 100.00 Using index for group-by 2 UNION t1 NULL range a a 5 NULL X 100.00 Using index for group-by NULL UNION RESULT NULL ALL NULL NULL NULL NULL X NULL Using temporary Warnings: Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `max(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` union /* select#2 */ select max(`test`.`t1`.`b`) AS `max(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` # In queries below "index for group-by" should be used for subqueries EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer NULL index NULL a 10 NULL X 100.00 Using index 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2)) AS `x` from `test`.`t1` `t1_outer` EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer NULL index NULL a 10 NULL X 100.00 Using index 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer` where true EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL X NULL Impossible WHERE 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer` where false EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer NULL index NULL a 10 NULL X 100.00 Using where; Using index 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a`,`test`.`t1_outer`.`a` in ( (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2) ), (`test`.`t1_outer`.`a` in on where ((`test`.`t1_outer`.`a` = `materialized-subquery`.`max(b)`))))) EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer NULL range a a 5 NULL X 100.00 Using index for group-by 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer` group by `test`.`t1_outer`.`a` having (`test`.`t1_outer`.`a` > (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2))) EXPLAIN SELECT 1 FROM t1 AS t1_outer1 LEFT JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer1 NULL index NULL a 10 NULL X 100.00 Using index 1 PRIMARY t1_outer2 NULL index NULL a 10 NULL X 100.00 Using where; Using index; Using join buffer (Block Nested Loop) 2 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` `t1_outer1` left join `test`.`t1` `t1_outer2` on(((`test`.`t1_outer2`.`b` = `test`.`t1_outer1`.`b`) and (`test`.`t1_outer1`.`a` = (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2))))) where true EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_outer2 NULL index NULL a 10 NULL X 100.00 Using index 2 SUBQUERY t1_outer NULL index NULL a 10 NULL X 100.00 Using index 3 SUBQUERY t1 NULL range a a 5 NULL X 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select (/* select#2 */ select (/* select#3 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2)) AS `x` from `test`.`t1` `t1_outer`) AS `x2` from `test`.`t1` `t1_outer2` CREATE TABLE t3 LIKE t1; # Ensure that t3 is loaded into data-dictionary cache. FLUSH STATUS; INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 12 Handler_read_next 0 DELETE FROM t3; FLUSH STATUS; INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) FROM t1 LIMIT 1; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 13 Handler_read_next 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 12 Handler_read_next 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1) > 10000; ERROR 21000: Subquery returns more than 1 row SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 13 Handler_read_next 1 DROP TABLE t1,t2,t3;