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
|
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
PRIMARY KEY pk (pk),
KEY idx_a (a),
KEY idx_b (b),
KEY idx_c (c),
KEY idx_d (d)
);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # Using intersect(idx_b,idx_c); Using where
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ;
COUNT(*) SUM(a)
15 90
UPDATE t1 SET a=2 WHERE b=6 AND c=6 ;
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ;
COUNT(*) SUM(a)
15 30
EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_x,idx_x,idx_x idx_x,idx_x 4,4 NULL # Using intersect(idx_x,idx_x); Using where
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6;
COUNT(*) SUM(a)
15 30
UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6;
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6;
COUNT(*) SUM(a)
15 30
EXPLAIN UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_b,idx_d idx_b,idx_d 4,4 NULL # Using intersect(idx_b,idx_d); Using where
SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
COUNT(*) SUM(c)
15 90
UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6;
SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
COUNT(*) SUM(c)
15 90
EXPLAIN UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # Using intersect(idx_a,idx_d); Using where
SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6;
COUNT(*) SUM(b)
0 NULL
UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6;
SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6;
COUNT(*) SUM(b)
0 NULL
EXPLAIN UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # Using intersect(idx_a,idx_d); Using where; Using temporary
SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
COUNT(*) SUM(a)
0 NULL
UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5);
COUNT(*) SUM(a)
0 NULL
EXPLAIN UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx_a,idx_b,idx_d idx_a 4 const # Using where; Using temporary
UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3;
create table t2 (
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
PRIMARY KEY pk (pk),
KEY idx_a (a),
KEY idx_b (b),
KEY idx_c (c),
KEY idx_d (d)
)engine=InnoDB;
INSERT INTO t2 SELECT * FROM t1;
ANALYZE TABLE t2;
Table Op Msg_type Msg_text
test.t2 analyze status OK
EXPLAIN UPDATE t2 SET a=2 WHERE pk<2492 AND d=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range PRIMARY,idx_d PRIMARY 3 const # Using where
UPDATE t2 SET a=2 WHERE pk<2492 AND d=1;
CREATE TABLE t3(
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
e INT
);
INSERT INTO t3(a,b,c,d,e) VALUES (3890,3890,3890,3890,3890);
INSERT INTO t3(a,b,c,d,e) VALUES (4000,4000,4000,4000,4000);
EXPLAIN REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # Using intersect(idx_b,idx_c); Using where
SELECT COUNT(*) FROM t3;
COUNT(*)
2
REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6;
SELECT COUNT(*) FROM t3;
COUNT(*)
17
EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge idx_a,idx_b,idx_d idx_b,idx_d 4,4 NULL # Using intersect(idx_b,idx_d); Using where
SELECT COUNT(*) FROM t3;
COUNT(*)
17
INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6;
SELECT COUNT(*) FROM t3;
COUNT(*)
32
CREATE TABLE t4 (
pk MEDIUMINT NOT NULL AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
PRIMARY KEY pk (pk),
KEY idx_cd (c,d),
KEY idx_bd (b,d)
);
INSERT INTO t4 SELECT * FROM t1;
ANALYZE TABLE t4;
Table Op Msg_type Msg_text
test.t4 analyze status Table is already up to date
EXPLAIN UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 index_merge idx_xd,idx_xd idx_xd,idx_xd 8,8 NULL # Using intersect(idx_xd,idx_xd); Using where
SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6;
COUNT(*) SUM(a)
15 30
UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6;
SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6;
COUNT(*) SUM(a)
15 30
DROP TABLE t1,t2,t3,t4;
|
