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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
CREATE TABLE t1 (c1 INT NOT NULL, c2 varchar (64), PRIMARY KEY (c1))
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (0)
(SUBPARTITION subp0,
SUBPARTITION subp1),
PARTITION p1 VALUES LESS THAN (100000)
(SUBPARTITION subp6,
SUBPARTITION subp7));
CREATE TABLE t2 (c1 int);
CREATE TABLE t3 LIKE t2;
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 SELECT c1 FROM t1;
INSERT INTO t3 SELECT c1 FROM t2;
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
test.t3 analyze status OK
CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1 > 20;
CREATE VIEW v2 AS SELECT t1.* FROM t1 JOIN t2 ON t1.c2=t2.c1;
EXPLAIN
DELETE LOW_PRIORITY QUICK IGNORE
FROM t1 PARTITION (p1)
WHERE c1 > 0
ORDER BY c2
LIMIT 10;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 DELETE t1 p1_subp6,p1_subp7 range PRIMARY PRIMARY 4 const 3 100.00 Using where; Using filesort
Warnings:
Note 1003 delete low_priority quick ignore from `test`.`t1` PARTITION (`p1`) where (`test`.`t1`.`c1` > 0) order by `test`.`t1`.`c2` limit 10
EXPLAIN
DELETE LOW_PRIORITY QUICK IGNORE t1 , t2
FROM t1, t2, t3
WHERE t1.c1 > 0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 DELETE t1 p1_subp6,p1_subp7 ALL PRIMARY NULL NULL NULL 3 100.00 Using where
1 DELETE t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 delete low_priority quick ignore `test`.`t1`, `test`.`t2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t1`.`c1` > 0)
EXPLAIN
UPDATE LOW_PRIORITY IGNORE t1
SET c1 = 20
WHERE c1 > 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t1 p1_subp6,p1_subp7 range PRIMARY PRIMARY 4 const 1 100.00 Using where; Using temporary
Warnings:
Note 1003 update low_priority ignore `test`.`t1` set `test`.`t1`.`c1` = 20 where (`test`.`t1`.`c1` > 100)
EXPLAIN
UPDATE LOW_PRIORITY IGNORE t1 LEFT JOIN t2 ON t1.c1 = t2.c1
SET t1.c1 = 20
WHERE t1.c1 > 0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t1 p1_subp6,p1_subp7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 update low_priority ignore `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`c1` = `test`.`t1`.`c1`)) set `test`.`t1`.`c1` = 20 where (`test`.`t1`.`c1` > 0)
EXPLAIN UPDATE v1 SET c2=c1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t1 p1_subp6,p1_subp7 range PRIMARY PRIMARY 4 const 1 100.00 Using where
Warnings:
Note 1003 update (`test`.`t1`) set `test`.`t1`.`c2` = `test`.`t1`.`c1` where (`test`.`t1`.`c1` > 20)
EXPLAIN UPDATE v2 SET c2=c1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
Warnings:
Note 1003 update (`test`.`t1` join `test`.`t2`) set `test`.`t1`.`c2` = `test`.`t1`.`c1` where (cast(`test`.`t1`.`c2` as double) = cast(`test`.`t2`.`c1` as double))
EXPLAIN
INSERT LOW_PRIORITY IGNORE INTO t1 PARTITION(p0, p1) (c1, c2)
VALUES (1, 'a'), (2, 'b')
ON DUPLICATE KEY UPDATE c2 = 'c';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t1 p1_subp6,p1_subp7 ALL NULL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 insert low_priority ignore into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`,`test`.`t1`.`c2`) values (1,'a'),(2,'b') on duplicate key update `test`.`t1`.`c2` = 'c'
EXPLAIN
INSERT HIGH_PRIORITY IGNORE INTO t1 PARTITION(p0, p1) (c1, c2)
VALUES (1, 'a'), (2, 'b')
ON DUPLICATE KEY UPDATE c2 = 'c';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t1 p1_subp6,p1_subp7 ALL NULL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 insert high_priority ignore into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`,`test`.`t1`.`c2`) values (1,'a'),(2,'b') on duplicate key update `test`.`t1`.`c2` = 'c'
EXPLAIN
INSERT DELAYED IGNORE INTO t1 PARTITION(p0, p1) (c1, c2)
SELECT c1, 'a' FROM t2
ON DUPLICATE KEY UPDATE c2 = 'c' ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
Note 1003 insert ignore into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`,`test`.`t1`.`c2`) /* select#1 */ select `test`.`t2`.`c1` AS `c1`,'a' AS `a` from `test`.`t2` on duplicate key update `test`.`t1`.`c2` = 'c'
EXPLAIN
INSERT INTO t1 PARTITION(p0, p1)
SET c1 = (SELECT c1 from t2 LIMIT 1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t1 p1_subp6 ALL NULL NULL NULL NULL NULL NULL NULL
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 insert into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`) values ((/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` limit 1))
EXPLAIN
REPLACE LOW_PRIORITY INTO t1 PARTITION(p0, p1) (c1, c2)
VALUES (1, 'a'), (2, 'b');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 REPLACE t1 p1_subp6,p1_subp7 ALL NULL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 replace low_priority into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`,`test`.`t1`.`c2`) values (1,'a'),(2,'b')
EXPLAIN
REPLACE DELAYED INTO t1 PARTITION(p0, p1) (c1, c2)
SELECT c1, 'a' FROM t2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 REPLACE t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Warning 3005 REPLACE DELAYED is no longer supported. The statement was converted to REPLACE.
Note 1003 replace into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`,`test`.`t1`.`c2`) /* select#1 */ select `test`.`t2`.`c1` AS `c1`,'a' AS `a` from `test`.`t2`
EXPLAIN
REPLACE INTO t1 PARTITION(p0, p1)
SET c1 = (SELECT c1 from t2 LIMIT 1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 REPLACE t1 p1_subp6 ALL NULL NULL NULL NULL NULL NULL NULL
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 replace into `test`.`t1` PARTITION (`p0`,`p1`) (`test`.`t1`.`c1`) values ((/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` limit 1))
EXPLAIN FORMAT=TRADITIONAL FOR QUERY 'DELETE FROM t3 WHERE c1 > 0'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 DELETE t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
EXPLAIN FORMAT=TRADITIONAL FOR QUERY 'UPDATE LOW_PRIORITY IGNORE t1 LEFT JOIN t2 ON t1.c1 = t2.c1
SET t1.c1 = 20
WHERE t1.c1 > 0'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t1 p1_subp6,p1_subp7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
EXPLAIN
INSERT /*+ NO_BNL(t2@QB1) */ INTO t3
(SELECT /*+ QB_NAME(qb1) */ t2.c1
FROM t1,t2
WHERE t1.c2 = t2.c1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 ALL NULL NULL NULL NULL 3 33.33 Using where; Using join buffer (hash join)
Warnings:
Note 1003 insert /*+ NO_BNL(`t2`@`qb1`) */ into `test`.`t3` /* select#1 */ select /*+ QB_NAME(`qb1`) */ `test`.`t2`.`c1` AS `c1` from `test`.`t1` join `test`.`t2` where (cast(`test`.`t1`.`c2` as double) = cast(`test`.`t2`.`c1` as double))
EXPLAIN
INSERT INTO t3
(SELECT /*+ NO_ICP(t2) */ t2.c1
FROM t1,t2
WHERE t1.c2 = t2.c1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where; Using join buffer (hash join)
Warnings:
Note 1003 insert /*+ NO_ICP(`t2`@`select#1`) */ into `test`.`t3` /* select#1 */ select `test`.`t2`.`c1` AS `c1` from `test`.`t1` join `test`.`t2` where (cast(`test`.`t1`.`c2` as double) = cast(`test`.`t2`.`c1` as double))
EXPLAIN
INSERT INTO t3
(SELECT /*+ NO_ICP(t2) */ t2.c1
FROM t2
WHERE t2.c1 IN (SELECT /*+ NO_ICP(t1) */ t1.c1
FROM t1
WHERE t1.c2 BETWEEN 'a' AND 'z'));
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t1 p0_subp0,p0_subp1,p1_subp6,p1_subp7 eq_ref PRIMARY PRIMARY 4 test.t2.c1 1 33.33 Using where
Warnings:
Note 1003 insert /*+ NO_ICP(`t1`@`select#2`) NO_ICP(`t2`@`select#1`) */ into `test`.`t3` /* select#1 */ select `test`.`t2`.`c1` AS `c1` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c1` = `test`.`t2`.`c1`) and (`test`.`t1`.`c2` between 'a' and 'z'))
DROP VIEW v1, v2;
DROP TABLE t1, t2, t3;
|
