Debian Codegraph @

mysql-test/suite/perfschema/r/idx_data_locks.result

raw
1 # Connection con1
2 create database explain_test_db;
3 create table explain_test_db.explain_test_table(a int, b int, PRIMARY KEY (a, b)) engine=innodb
4 PARTITION BY RANGE( a )
5 SUBPARTITION BY HASH( b ) (
6 PARTITION p0 VALUES LESS THAN (1990) (
7 SUBPARTITION s0,
8 SUBPARTITION s1
9 ),
10 PARTITION p1 VALUES LESS THAN (2000) (
11 SUBPARTITION s2,
12 SUBPARTITION s3
13 ),
14 PARTITION p2 VALUES LESS THAN MAXVALUE (
15 SUBPARTITION s4,
16 SUBPARTITION s5
17 )
18 );
19 insert into explain_test_db.explain_test_table values (1, 100), (1995, 200);
20 start transaction;
21 update explain_test_db.explain_test_table set b=b+1;
22 # Connection default
23 select ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID,
24 PARTITION_NAME, SUBPARTITION_NAME
25 from performance_schema.data_locks
26 where OBJECT_NAME = 'explain_test_table'
27 and LOCK_TYPE = 'RECORD' and LOCK_DATA = '1, 100'
28 into @engine, @lock_id, @trx_id, @thread_id, @event_id, @part, @subpart;
29 select @engine is null,
30 @lock_id is null,
31 @trx_id is null,
32 @thread_id is null,
33 @event_id is null,
34 @part, @subpart;
35 @engine is null @lock_id is null @trx_id is null @thread_id is null @event_id is null @part @subpart
36 0 0 0 0 0 p0 s0
37
38 ====================================================================
39 Testing index for columns ENGINE_LOCK_ID, ENGINE
40 ====================================================================
41 ############ Explain for Query ####################################
42 explain select COUNT(*)
43 from performance_schema.data_locks
44 where ENGINE_LOCK_ID = "impossible";
45 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
46 1 SIMPLE data_locks NULL ref PRIMARY PRIMARY 514 const # 100.00 NULL
47 ############ Explain for Query ####################################
48 explain select COUNT(*)
49 from performance_schema.data_locks
50 where ENGINE_LOCK_ID > "impossible";
51 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
52 1 SIMPLE data_locks NULL ALL PRIMARY NULL NULL NULL # 33.33 Using where
53 ############ Explain for Query ####################################
54 explain select COUNT(*)
55 from performance_schema.data_locks
56 where ENGINE_LOCK_ID < "2";
57 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
58 1 SIMPLE data_locks NULL ALL PRIMARY NULL NULL NULL # 33.33 Using where
59 ############ Explain for Query ####################################
60 explain select COUNT(*)
61 from performance_schema.data_locks
62 where ENGINE_LOCK_ID = @lock_id;
63 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
64 1 SIMPLE data_locks NULL ref PRIMARY PRIMARY 514 const # 100.00 NULL
65 ############ Explain for Query ####################################
66 explain select COUNT(*)
67 from performance_schema.data_locks
68 where ENGINE_LOCK_ID = "impossible"
69 and ENGINE = "impossible";
70 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
71 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
72 ############ Explain for Query ####################################
73 explain select COUNT(*)
74 from performance_schema.data_locks
75 where ENGINE_LOCK_ID = @lock_id
76 and ENGINE = "impossible";
77 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
78 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
79 ############ Explain for Query ####################################
80 explain select COUNT(*)
81 from performance_schema.data_locks
82 where ENGINE_LOCK_ID = @lock_id
83 and ENGINE > "impossible";
84 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
85 1 SIMPLE data_locks NULL ref PRIMARY PRIMARY 514 const # 33.33 Using where
86 ############ Explain for Query ####################################
87 explain select COUNT(*)
88 from performance_schema.data_locks
89 where ENGINE_LOCK_ID = @lock_id
90 and ENGINE < "2";
91 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
92 1 SIMPLE data_locks NULL ref PRIMARY PRIMARY 514 const # 33.33 Using where
93 ############ Explain for Query ####################################
94 explain select COUNT(*)
95 from performance_schema.data_locks
96 where ENGINE_LOCK_ID = @lock_id
97 and ENGINE = @engine;
98 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
99 1 SIMPLE data_locks NULL const PRIMARY PRIMARY 644 const,const # 100.00 NULL
100 ############# Explain End #########################################
101 flush status;
102 select COUNT(*)
103 from performance_schema.data_locks
104 where ENGINE_LOCK_ID = @lock_id
105 and ENGINE = @engine;
106 COUNT(*)
107 #
108 OK: handler_read_key incremented
109
110 ====================================================================
111 Testing index for columns ENGINE_TRANSACTION_ID, ENGINE
112 ====================================================================
113 ############ Explain for Query ####################################
114 explain select COUNT(*)
115 from performance_schema.data_locks
116 where ENGINE_TRANSACTION_ID = "impossible";
117 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
118 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 9 const # 100.00 Using where
119 ############ Explain for Query ####################################
120 explain select COUNT(*)
121 from performance_schema.data_locks
122 where ENGINE_TRANSACTION_ID > "impossible";
123 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
124 1 SIMPLE data_locks NULL ALL ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
125 ############ Explain for Query ####################################
126 explain select COUNT(*)
127 from performance_schema.data_locks
128 where ENGINE_TRANSACTION_ID < "2";
129 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
130 1 SIMPLE data_locks NULL ALL ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
131 ############ Explain for Query ####################################
132 explain select COUNT(*)
133 from performance_schema.data_locks
134 where ENGINE_TRANSACTION_ID = @trx_id;
135 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
136 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 9 const # 100.00 NULL
137 ############ Explain for Query ####################################
138 explain select COUNT(*)
139 from performance_schema.data_locks
140 where ENGINE_TRANSACTION_ID = "impossible"
141 and ENGINE = "impossible";
142 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
143 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 139 const,const # 100.00 Using where
144 ############ Explain for Query ####################################
145 explain select COUNT(*)
146 from performance_schema.data_locks
147 where ENGINE_TRANSACTION_ID = @trx_id
148 and ENGINE = "impossible";
149 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
150 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
151 ############ Explain for Query ####################################
152 explain select COUNT(*)
153 from performance_schema.data_locks
154 where ENGINE_TRANSACTION_ID = @trx_id
155 and ENGINE > "impossible";
156 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
157 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
158 ############ Explain for Query ####################################
159 explain select COUNT(*)
160 from performance_schema.data_locks
161 where ENGINE_TRANSACTION_ID = @trx_id
162 and ENGINE < "2";
163 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
164 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
165 ############ Explain for Query ####################################
166 explain select COUNT(*)
167 from performance_schema.data_locks
168 where ENGINE_TRANSACTION_ID = @trx_id
169 and ENGINE = @engine;
170 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
171 1 SIMPLE data_locks NULL ref ENGINE_TRANSACTION_ID ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
172 ############# Explain End #########################################
173 flush status;
174 select COUNT(*)
175 from performance_schema.data_locks
176 where ENGINE_TRANSACTION_ID = @trx_id
177 and ENGINE = @engine;
178 COUNT(*)
179 #
180 OK: handler_read_key incremented
181
182 ====================================================================
183 Testing index for columns THREAD_ID, EVENT_ID
184 ====================================================================
185 ############ Explain for Query ####################################
186 explain select COUNT(*)
187 from performance_schema.data_locks
188 where THREAD_ID = "impossible";
189 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
190 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 9 const # 100.00 Using where
191 ############ Explain for Query ####################################
192 explain select COUNT(*)
193 from performance_schema.data_locks
194 where THREAD_ID > "impossible";
195 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
196 1 SIMPLE data_locks NULL ALL THREAD_ID NULL NULL NULL # 33.33 Using where
197 ############ Explain for Query ####################################
198 explain select COUNT(*)
199 from performance_schema.data_locks
200 where THREAD_ID < "2";
201 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
202 1 SIMPLE data_locks NULL ALL THREAD_ID NULL NULL NULL # 33.33 Using where
203 ############ Explain for Query ####################################
204 explain select COUNT(*)
205 from performance_schema.data_locks
206 where THREAD_ID = @thread_id;
207 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
208 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 9 const # 100.00 NULL
209 ############ Explain for Query ####################################
210 explain select COUNT(*)
211 from performance_schema.data_locks
212 where THREAD_ID = "impossible"
213 and EVENT_ID = "impossible";
214 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
215 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 18 const,const # 100.00 Using where
216 ############ Explain for Query ####################################
217 explain select COUNT(*)
218 from performance_schema.data_locks
219 where THREAD_ID = @thread_id
220 and EVENT_ID = "impossible";
221 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
222 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 18 const,const # 100.00 Using where
223 ############ Explain for Query ####################################
224 explain select COUNT(*)
225 from performance_schema.data_locks
226 where THREAD_ID = @thread_id
227 and EVENT_ID > "impossible";
228 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
229 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 9 const # 33.33 Using where
230 ############ Explain for Query ####################################
231 explain select COUNT(*)
232 from performance_schema.data_locks
233 where THREAD_ID = @thread_id
234 and EVENT_ID < "2";
235 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
236 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 9 const # 33.33 Using where
237 ############ Explain for Query ####################################
238 explain select COUNT(*)
239 from performance_schema.data_locks
240 where THREAD_ID = @thread_id
241 and EVENT_ID = @event_id;
242 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
243 1 SIMPLE data_locks NULL ref THREAD_ID THREAD_ID 18 const,const # 100.00 NULL
244 ############# Explain End #########################################
245 flush status;
246 select COUNT(*)
247 from performance_schema.data_locks
248 where THREAD_ID = @thread_id
249 and EVENT_ID = @event_id;
250 COUNT(*)
251 #
252 OK: handler_read_key incremented
253
254 ====================================================================
255 Testing index for columns OBJECT_SCHEMA, OBJECT_NAME, PARTITION_NAME, SUBPARTITION_NAME
256 ====================================================================
257 ############ Explain for Query ####################################
258 explain select COUNT(*)
259 from performance_schema.data_locks
260 where OBJECT_SCHEMA = "impossible";
261 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
262 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 259 const # 100.00 NULL
263 ############ Explain for Query ####################################
264 explain select COUNT(*)
265 from performance_schema.data_locks
266 where OBJECT_SCHEMA > "impossible";
267 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
268 1 SIMPLE data_locks NULL ALL OBJECT_SCHEMA NULL NULL NULL # 33.33 Using where
269 ############ Explain for Query ####################################
270 explain select COUNT(*)
271 from performance_schema.data_locks
272 where OBJECT_SCHEMA < "2";
273 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
274 1 SIMPLE data_locks NULL ALL OBJECT_SCHEMA NULL NULL NULL # 33.33 Using where
275 ############ Explain for Query ####################################
276 explain select COUNT(*)
277 from performance_schema.data_locks
278 where OBJECT_SCHEMA = "explain_test_db";
279 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
280 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 259 const # 100.00 NULL
281 ############ Explain for Query ####################################
282 explain select COUNT(*)
283 from performance_schema.data_locks
284 where OBJECT_SCHEMA = "impossible"
285 and OBJECT_NAME = "impossible";
286 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
287 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 518 const,const # 100.00 NULL
288 ############ Explain for Query ####################################
289 explain select COUNT(*)
290 from performance_schema.data_locks
291 where OBJECT_SCHEMA = "explain_test_db"
292 and OBJECT_NAME = "impossible";
293 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
294 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 518 const,const # 100.00 NULL
295 ############ Explain for Query ####################################
296 explain select COUNT(*)
297 from performance_schema.data_locks
298 where OBJECT_SCHEMA = "explain_test_db"
299 and OBJECT_NAME > "impossible";
300 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
301 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 259 const # 33.33 Using where
302 ############ Explain for Query ####################################
303 explain select COUNT(*)
304 from performance_schema.data_locks
305 where OBJECT_SCHEMA = "explain_test_db"
306 and OBJECT_NAME < "2";
307 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
308 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 259 const # 33.33 Using where
309 ############ Explain for Query ####################################
310 explain select COUNT(*)
311 from performance_schema.data_locks
312 where OBJECT_SCHEMA = "explain_test_db"
313 and OBJECT_NAME = "explain_test_table";
314 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
315 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 518 const,const # 100.00 NULL
316 ############ Explain for Query ####################################
317 explain select COUNT(*)
318 from performance_schema.data_locks
319 where OBJECT_SCHEMA = "impossible"
320 and OBJECT_NAME = "impossible"
321 and PARTITION_NAME = "impossible";
322 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
323 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 777 const,const,const # 100.00 NULL
324 ############ Explain for Query ####################################
325 explain select COUNT(*)
326 from performance_schema.data_locks
327 where OBJECT_SCHEMA = "explain_test_db"
328 and OBJECT_NAME = "explain_test_table"
329 and PARTITION_NAME = "impossible";
330 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
331 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 777 const,const,const # 100.00 NULL
332 ############ Explain for Query ####################################
333 explain select COUNT(*)
334 from performance_schema.data_locks
335 where OBJECT_SCHEMA = "explain_test_db"
336 and OBJECT_NAME = "explain_test_table"
337 and PARTITION_NAME > "impossible";
338 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
339 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 518 const,const # 33.33 Using where
340 ############ Explain for Query ####################################
341 explain select COUNT(*)
342 from performance_schema.data_locks
343 where OBJECT_SCHEMA = "explain_test_db"
344 and OBJECT_NAME = "explain_test_table"
345 and PARTITION_NAME < "2";
346 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
347 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 518 const,const # 33.33 Using where
348 ############ Explain for Query ####################################
349 explain select COUNT(*)
350 from performance_schema.data_locks
351 where OBJECT_SCHEMA = "explain_test_db"
352 and OBJECT_NAME = "explain_test_table"
353 and PARTITION_NAME = @part;
354 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
355 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 777 const,const,const # 100.00 NULL
356 ############ Explain for Query ####################################
357 explain select COUNT(*)
358 from performance_schema.data_locks
359 where OBJECT_SCHEMA = "impossible"
360 and OBJECT_NAME = "impossible"
361 and PARTITION_NAME = "impossible"
362 and SUBPARTITION_NAME = "impossible";
363 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
364 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 1036 const,const,const,const # 100.00 NULL
365 ############ Explain for Query ####################################
366 explain select COUNT(*)
367 from performance_schema.data_locks
368 where OBJECT_SCHEMA = "explain_test_db"
369 and OBJECT_NAME = "explain_test_table"
370 and PARTITION_NAME = @part
371 and SUBPARTITION_NAME = "impossible";
372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
373 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 1036 const,const,const,const # 100.00 NULL
374 ############ Explain for Query ####################################
375 explain select COUNT(*)
376 from performance_schema.data_locks
377 where OBJECT_SCHEMA = "explain_test_db"
378 and OBJECT_NAME = "explain_test_table"
379 and PARTITION_NAME = @part
380 and SUBPARTITION_NAME > "impossible";
381 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
382 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 777 const,const,const # 33.33 Using where
383 ############ Explain for Query ####################################
384 explain select COUNT(*)
385 from performance_schema.data_locks
386 where OBJECT_SCHEMA = "explain_test_db"
387 and OBJECT_NAME = "explain_test_table"
388 and PARTITION_NAME = @part
389 and SUBPARTITION_NAME < "impossible";
390 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
391 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 777 const,const,const # 33.33 Using where
392 ############ Explain for Query ####################################
393 explain select COUNT(*)
394 from performance_schema.data_locks
395 where OBJECT_SCHEMA = "explain_test_db"
396 and OBJECT_NAME = "explain_test_table"
397 and PARTITION_NAME = @part
398 and SUBPARTITION_NAME = @subpart;
399 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
400 1 SIMPLE data_locks NULL ref OBJECT_SCHEMA OBJECT_SCHEMA 1036 const,const,const,const # 100.00 NULL
401 ############# Explain End #########################################
402 flush status;
403 select COUNT(*)
404 from performance_schema.data_locks
405 where OBJECT_SCHEMA = "explain_test_db"
406 and OBJECT_NAME = "explain_test_table"
407 and PARTITION_NAME = @part
408 and SUBPARTITION_NAME = @subpart;
409 COUNT(*)
410 #
411 OK: handler_read_key incremented
412 # Connection con1
413 commit;
414 drop table explain_test_db.explain_test_table;
415 drop database explain_test_db;
416 # Connection default