Debian Codegraph @

mysql-test/suite/ndb_opt/statistics0.result

raw
1 set @is_enable_default = @@global.ndb_index_stat_enable;
2 set @is_enable = 0;
3 set @is_enable = NULL;
4 # is_enable_on=0 is_enable_off=1
5 # ndb_index_stat_enable - before
6 show global variables like 'ndb_index_stat_enable';
7 Variable_name Value
8 ndb_index_stat_enable ON
9 show local variables like 'ndb_index_stat_enable';
10 Variable_name Value
11 ndb_index_stat_enable ON
12 set @@local.ndb_index_stat_enable = 0;
13 set @@global.ndb_index_stat_enable = 0;
14 # ndb_index_stat_enable - after
15 show global variables like 'ndb_index_stat_enable';
16 Variable_name Value
17 ndb_index_stat_enable OFF
18 show local variables like 'ndb_index_stat_enable';
19 Variable_name Value
20 ndb_index_stat_enable OFF
21 CREATE TABLE t10(
22 K INT NOT NULL AUTO_INCREMENT,
23 I INT, J INT,
24 PRIMARY KEY(K),
25 KEY(I,J),
26 UNIQUE KEY(J,K)
27 ) ENGINE=ndbcluster
28 partition by key (K) partitions 1;
29 INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
30 CREATE TABLE t100 LIKE t10;
31 INSERT INTO t100(I,J)
32 SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y;
33 CREATE TABLE t10000 LIKE t10;
34 ALTER TABLE t10000 ENGINE=INNODB;
35 INSERT INTO t10000(I,J)
36 SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y;
37 ALTER TABLE t10000 ENGINE=NDBCLUSTER;
38 ANALYZE TABLE t10,t100,t10000;
39 Table Op Msg_type Msg_text
40 test.t10 analyze status OK
41 test.t100 analyze status OK
42 test.t10000 analyze status OK
43 SELECT COUNT(*) FROM t10;
44 COUNT(*)
45 10
46 SELECT COUNT(*) FROM t100;
47 COUNT(*)
48 100
49 SELECT COUNT(*) FROM t10000;
50 COUNT(*)
51 10000
52 EXPLAIN
53 SELECT * FROM t10000 WHERE k = 42;
54 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
55 1 SIMPLE t10000 p0 eq_ref PRIMARY PRIMARY 4 const 1 100.00 NULL
56 Warnings:
57 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` = 42)
58 EXPLAIN
59 SELECT * FROM t10000 WHERE k >= 42 and k < 10000;
60 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
61 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 500 100.00 Using pushed condition ((`test`.`t10000`.`K` >= 42) and (`test`.`t10000`.`K` < 10000)); Using MRR
62 Warnings:
63 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`K` >= 42) and (`test`.`t10000`.`K` < 10000))
64 EXPLAIN
65 SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
66 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
67 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 500 100.00 Using pushed condition (`test`.`t10000`.`K` between 42 and 10000); Using MRR
68 Warnings:
69 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` between 42 and 10000)
70 EXPLAIN
71 SELECT * FROM t10000 WHERE k < 42;
72 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
73 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 1000 100.00 Using pushed condition (`test`.`t10000`.`K` < 42); Using MRR
74 Warnings:
75 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` < 42)
76 EXPLAIN
77 SELECT * FROM t10000 WHERE k > 42;
78 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
79 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 1000 100.00 Using pushed condition (`test`.`t10000`.`K` > 42); Using MRR
80 Warnings:
81 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` > 42)
82 EXPLAIN
83 SELECT * FROM t10000 AS x JOIN t10000 AS y
84 ON y.i=x.i AND y.j = x.i;
85 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
86 1 SIMPLE x p0 ALL I NULL NULL NULL 10000 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`x`.`I` is not null)
87 1 SIMPLE y p0 ref J,I I 10 test.x.I,test.x.I 11 100.00 Child of 'x' in pushed join@1
88 Warnings:
89 Note 1003 /* select#1 */ select `test`.`x`.`K` AS `K`,`test`.`x`.`I` AS `I`,`test`.`x`.`J` AS `J`,`test`.`y`.`K` AS `K`,`test`.`y`.`I` AS `I`,`test`.`y`.`J` AS `J` from `test`.`t10000` `x` join `test`.`t10000` `y` where ((`test`.`y`.`I` = `test`.`x`.`I`) and (`test`.`y`.`J` = `test`.`x`.`I`))
90 EXPLAIN
91 SELECT * FROM t100 WHERE k < 42;
92 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
93 1 SIMPLE t100 p0 range PRIMARY PRIMARY 4 NULL 10 100.00 Using pushed condition (`test`.`t100`.`K` < 42); Using MRR
94 Warnings:
95 Note 1003 /* select#1 */ select `test`.`t100`.`K` AS `K`,`test`.`t100`.`I` AS `I`,`test`.`t100`.`J` AS `J` from `test`.`t100` where (`test`.`t100`.`K` < 42)
96 EXPLAIN
97 SELECT * FROM t100 WHERE k > 42;
98 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
99 1 SIMPLE t100 p0 range PRIMARY PRIMARY 4 NULL 10 100.00 Using pushed condition (`test`.`t100`.`K` > 42); Using MRR
100 Warnings:
101 Note 1003 /* select#1 */ select `test`.`t100`.`K` AS `K`,`test`.`t100`.`I` AS `I`,`test`.`t100`.`J` AS `J` from `test`.`t100` where (`test`.`t100`.`K` > 42)
102 EXPLAIN
103 SELECT * FROM t10000 WHERE k < 42;
104 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
105 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 1000 100.00 Using pushed condition (`test`.`t10000`.`K` < 42); Using MRR
106 Warnings:
107 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` < 42)
108 EXPLAIN
109 SELECT * FROM t10000 WHERE k > 42;
110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
111 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 1000 100.00 Using pushed condition (`test`.`t10000`.`K` > 42); Using MRR
112 Warnings:
113 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` > 42)
114 EXPLAIN
115 SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000;
116 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
117 1 SIMPLE t100 p0 range PRIMARY PRIMARY 4 NULL 5 100.00 Using pushed condition (`test`.`t100`.`K` between 42 and 10000); Using MRR
118 Warnings:
119 Note 1003 /* select#1 */ select `test`.`t100`.`K` AS `K`,`test`.`t100`.`I` AS `I`,`test`.`t100`.`J` AS `J` from `test`.`t100` where (`test`.`t100`.`K` between 42 and 10000)
120 EXPLAIN
121 SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000;
122 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
123 1 SIMPLE t10000 p0 range PRIMARY PRIMARY 4 NULL 500 100.00 Using pushed condition (`test`.`t10000`.`K` between 42 and 10000); Using MRR
124 Warnings:
125 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`K` between 42 and 10000)
126 EXPLAIN
127 SELECT * FROM t10000 WHERE I = 0;
128 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
129 1 SIMPLE t10000 p0 ref I I 5 const 200 100.00 NULL
130 Warnings:
131 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`I` = 0)
132 EXPLAIN
133 SELECT * FROM t10000 WHERE J = 0;
134 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
135 1 SIMPLE t10000 p0 ref J J 5 const 166 100.00 NULL
136 Warnings:
137 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`J` = 0)
138 EXPLAIN
139 SELECT * FROM t10000 WHERE I = 0 AND J = 0;
140 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
141 1 SIMPLE t10000 p0 ref J,I I 10 const,const 21 100.00 NULL
142 Warnings:
143 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`I` = 0))
144 EXPLAIN
145 SELECT * FROM t10000 WHERE I = 0;
146 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
147 1 SIMPLE t10000 p0 ref I I 5 const 200 100.00 NULL
148 Warnings:
149 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`I` = 0)
150 EXPLAIN
151 SELECT * FROM t10000 WHERE I = 0 AND J > 1;
152 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
153 1 SIMPLE t10000 p0 range J,I I 10 NULL 100 100.00 Using pushed condition ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` > 1)); Using MRR
154 Warnings:
155 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` > 1))
156 EXPLAIN
157 SELECT * FROM t10000 WHERE I = 0 AND J < 1;
158 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
159 1 SIMPLE t10000 p0 range J,I I 10 NULL 50 100.00 Using pushed condition ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` < 1)); Using MRR
160 Warnings:
161 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` < 1))
162 EXPLAIN
163 SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10;
164 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
165 1 SIMPLE t10000 p0 range J,I I 10 NULL 50 100.00 Using pushed condition ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` between 1 and 10)); Using MRR
166 Warnings:
167 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` between 1 and 10))
168 EXPLAIN
169 SELECT * FROM t10000 WHERE I = 0 AND J = 1;
170 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
171 1 SIMPLE t10000 p0 ref J,I I 10 const,const 21 100.00 NULL
172 Warnings:
173 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 1) and (`test`.`t10000`.`I` = 0))
174 EXPLAIN
175 SELECT * FROM t10000 WHERE J = 0;
176 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
177 1 SIMPLE t10000 p0 ref J J 5 const 166 100.00 NULL
178 Warnings:
179 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where (`test`.`t10000`.`J` = 0)
180 EXPLAIN
181 SELECT * FROM t10000 WHERE J = 0 AND K > 1;
182 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
183 1 SIMPLE t10000 p0 range PRIMARY,J J 9 NULL 83 100.00 Using pushed condition ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` > 1)); Using MRR
184 Warnings:
185 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` > 1))
186 EXPLAIN
187 SELECT * FROM t10000 WHERE J = 0 AND K < 1;
188 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
189 1 SIMPLE t10000 p0 range PRIMARY,J J 9 NULL 83 100.00 Using pushed condition ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` < 1)); Using MRR
190 Warnings:
191 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` < 1))
192 EXPLAIN
193 SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10;
194 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
195 1 SIMPLE t10000 p0 range PRIMARY,J J 9 NULL 41 100.00 Using pushed condition ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` between 1 and 10)); Using MRR
196 Warnings:
197 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 0) and (`test`.`t10000`.`K` between 1 and 10))
198 EXPLAIN
199 SELECT * FROM t10000 WHERE J = 0 AND K = 1;
200 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
201 1 SIMPLE t10000 p0 eq_ref PRIMARY,J PRIMARY 4 const 1 10.00 Using where
202 Warnings:
203 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`K` = 1) and (`test`.`t10000`.`J` = 0))
204 EXPLAIN
205 SELECT * FROM t10000 WHERE I = 0 AND J <> 1;
206 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
207 1 SIMPLE t10000 p0 range J,I I 10 NULL 150 100.00 Using pushed condition ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` <> 1)); Using MRR
208 Warnings:
209 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` <> 1))
210 EXPLAIN
211 SELECT * FROM t10000 WHERE I <> 0 AND J = 1;
212 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
213 1 SIMPLE t10000 p0 ref J,I J 5 const 166 15.00 Using pushed condition (`test`.`t10000`.`I` <> 0)
214 Warnings:
215 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 1) and (`test`.`t10000`.`I` <> 0))
216 EXPLAIN
217 SELECT * FROM t10000 WHERE I <> 0 AND J <> 1;
218 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
219 1 SIMPLE t10000 p0 range J,I J 5 NULL 1500 15.00 Using pushed condition ((`test`.`t10000`.`I` <> 0) and (`test`.`t10000`.`J` <> 1)); Using MRR
220 Warnings:
221 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` <> 0) and (`test`.`t10000`.`J` <> 1))
222 EXPLAIN
223 SELECT * FROM t10000 WHERE J <> 1 AND I = 0;
224 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
225 1 SIMPLE t10000 p0 range J,I I 10 NULL 150 100.00 Using pushed condition ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` <> 1)); Using MRR
226 Warnings:
227 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`I` = 0) and (`test`.`t10000`.`J` <> 1))
228 EXPLAIN
229 SELECT * FROM t10000 WHERE J = 1 AND I <> 0;
230 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
231 1 SIMPLE t10000 p0 ref J,I J 5 const 166 15.00 Using pushed condition (`test`.`t10000`.`I` <> 0)
232 Warnings:
233 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` = 1) and (`test`.`t10000`.`I` <> 0))
234 EXPLAIN
235 SELECT * FROM t10000 WHERE J <> 1 AND I <> 0;
236 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
237 1 SIMPLE t10000 p0 range J,I J 5 NULL 1500 15.00 Using pushed condition ((`test`.`t10000`.`J` <> 1) and (`test`.`t10000`.`I` <> 0)); Using MRR
238 Warnings:
239 Note 1003 /* select#1 */ select `test`.`t10000`.`K` AS `K`,`test`.`t10000`.`I` AS `I`,`test`.`t10000`.`J` AS `J` from `test`.`t10000` where ((`test`.`t10000`.`J` <> 1) and (`test`.`t10000`.`I` <> 0))
240 DROP TABLE t10,t100,t10000;
241 set @is_enable = @is_enable_default;
242 set @is_enable = NULL;
243 # is_enable_on=1 is_enable_off=0
244 # ndb_index_stat_enable - before
245 show global variables like 'ndb_index_stat_enable';
246 Variable_name Value
247 ndb_index_stat_enable OFF
248 show local variables like 'ndb_index_stat_enable';
249 Variable_name Value
250 ndb_index_stat_enable OFF
251 set @@global.ndb_index_stat_enable = 1;
252 set @@local.ndb_index_stat_enable = 1;
253 # ndb_index_stat_enable - after
254 show global variables like 'ndb_index_stat_enable';
255 Variable_name Value
256 ndb_index_stat_enable ON
257 show local variables like 'ndb_index_stat_enable';
258 Variable_name Value
259 ndb_index_stat_enable ON