Debian Codegraph @

mysql-test/suite/ndb_opt/statistics1.result

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