316
316
INSERT into t2 values (1,1,1), (2,2,2);
317
317
optimize table t1;
318
318
Table Op Msg_type Msg_text
319
test.t1 optimize status OK
319
test.t1 optimize note The storage engine for the table doesn't support optimize
320
320
show index from t1;
321
321
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
322
t1 1 b 1 b A 5 NULL NULL YES BTREE
323
t1 1 c 1 c A 5 NULL NULL YES BTREE
324
t1 1 a 1 a A 1 NULL NULL BTREE
325
t1 1 a 2 b A 5 NULL NULL YES BTREE
326
t1 1 c_2 1 c A 5 NULL NULL YES BTREE
327
t1 1 c_2 2 a A 5 NULL NULL BTREE
322
t1 1 b 1 b A NULL NULL NULL YES BTREE
323
t1 1 c 1 c A NULL NULL NULL YES BTREE
324
t1 1 a 1 a A NULL NULL NULL BTREE
325
t1 1 a 2 b A NULL NULL NULL YES BTREE
326
t1 1 c_2 1 c A NULL NULL NULL YES BTREE
327
t1 1 c_2 2 a A NULL NULL NULL BTREE
328
328
explain select * from t1,t2 where t1.a=t2.a;
329
329
id select_type table type possible_keys key key_len ref rows Extra
330
330
1 SIMPLE t2 ALL a NULL NULL NULL 2
331
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer
331
1 SIMPLE t1 ref a a 4 test.t2.a 1
332
332
explain select * from t1,t2 force index(a) where t1.a=t2.a;
333
333
id select_type table type possible_keys key key_len ref rows Extra
334
334
1 SIMPLE t2 ALL a NULL NULL NULL 2
335
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer
335
1 SIMPLE t1 ref a a 4 test.t2.a 1
336
336
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
337
337
id select_type table type possible_keys key key_len ref rows Extra
338
338
1 SIMPLE t2 ALL a NULL NULL NULL 2
339
1 SIMPLE t1 ref a a 4 test.t2.a 3
339
1 SIMPLE t1 ref a a 4 test.t2.a 1
340
340
explain select * from t1,t2 where t1.b=t2.b;
341
341
id select_type table type possible_keys key key_len ref rows Extra
342
342
1 SIMPLE t2 ALL b NULL NULL NULL 2
343
1 SIMPLE t1 ref b b 5 test.t2.b 1
343
1 SIMPLE t1 ALL b NULL NULL NULL 5 Using where; Using join buffer
344
344
explain select * from t1,t2 force index(c) where t1.a=t2.a;
345
345
id select_type table type possible_keys key key_len ref rows Extra
346
346
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
347
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer
347
1 SIMPLE t1 ref a a 4 test.t2.a 1
348
348
explain select * from t1 where a=0 or a=2;
349
349
id select_type table type possible_keys key key_len ref rows Extra
350
350
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
added
removed
tests/r/myisam.result
