1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
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 24
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
3 UNION RESULT <union1,2> 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 <in_optimizer>(`test`.`t1_outer`.`a`,`test`.`t1_outer`.`a` in ( <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having (`test`.`t1`.`a` < 2) ), <primary_index_lookup>(`test`.`t1_outer`.`a` in <temporary table> on <auto_distinct_key> 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 (hash join)
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;
|
