1943
1943
auto int NULL NO PRI NULL auto_increment #
1944
1944
fld1 int unsigned NULL NO UNI NULL #
1945
1945
companynr tinyint(3) unsigned NULL NO NULL #
1946
fld3 varchar(30) latin1_swedish_ci NO MUL NULL #
1947
fld4 varchar(35) latin1_swedish_ci NO NULL #
1948
fld5 varchar(35) latin1_swedish_ci NO NULL #
1949
fld6 varchar(4) latin1_swedish_ci NO NULL #
1946
fld3 varchar(30) utf8_general_ci NO MUL NULL #
1947
fld4 varchar(35) utf8_general_ci NO NULL #
1948
fld5 varchar(35) utf8_general_ci NO NULL #
1949
fld6 varchar(4) utf8_general_ci NO NULL #
1950
1950
show full columns from t2 from test like 'f%';
1951
1951
Field Type Collation Null Key Default Extra Privileges Comment
1952
1952
fld1 int unsigned NULL NO UNI NULL #
1953
fld3 varchar(30) latin1_swedish_ci NO MUL NULL #
1954
fld4 varchar(35) latin1_swedish_ci NO NULL #
1955
fld5 varchar(35) latin1_swedish_ci NO NULL #
1956
fld6 varchar(4) latin1_swedish_ci NO NULL #
1953
fld3 varchar(30) utf8_general_ci NO MUL NULL #
1954
fld4 varchar(35) utf8_general_ci NO NULL #
1955
fld5 varchar(35) utf8_general_ci NO NULL #
1956
fld6 varchar(4) utf8_general_ci NO NULL #
1957
1957
show full columns from t2 from test like 's%';
1958
1958
Field Type Collation Null Key Default Extra Privileges Comment
1959
1959
drop table t4, t3, t2, t1;
2457
2457
insert into t1 values(1,""),(2,"");
2458
2458
show table status like 't1%';
2459
2459
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
2460
t1 InnoDB 10 Compact 2 8192 X X X X X X X X latin1_swedish_ci NULL
2461
t11 InnoDB 10 Compact 0 0 X X X X X X X X latin1_swedish_ci NULL
2460
t1 InnoDB 10 Compact 2 8192 X X X X X X X X utf8_general_ci NULL
2461
t11 InnoDB 10 Compact 0 0 X X X X X X X X utf8_general_ci NULL
2462
2462
select 123 as a from t1 where f1 is null;
2464
2464
drop table t1,t11;
3061
3061
FROM t1 JOIN t2 ON t2.fk=t1.pk
3062
3062
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
3063
3063
id select_type table type possible_keys key key_len ref rows Extra
3064
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
3065
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3064
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where
3065
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3066
3066
EXPLAIN SELECT t2.*
3067
3067
FROM t1 JOIN t2 ON t2.fk=t1.pk
3068
3068
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
3069
3069
id select_type table type possible_keys key key_len ref rows Extra
3070
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
3071
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3070
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where
3071
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3072
3072
EXPLAIN SELECT t2.*
3073
3073
FROM t1 JOIN t2 ON t2.fk=t1.pk
3074
3074
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
3075
3075
id select_type table type possible_keys key key_len ref rows Extra
3076
1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
3077
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
3076
1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where
3077
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where
3078
3078
DROP TABLE t1,t2;
3079
3079
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
3080
3080
CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
3084
3084
EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
3085
3085
id select_type table type possible_keys key key_len ref rows Extra
3086
3086
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3087
1 SIMPLE t2 const b b 22 const 1 Using index
3087
1 SIMPLE t2 const b b 82 const 1 Using index
3088
3088
DROP TABLE t1,t2;
3089
3089
CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
3090
3090
CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
3116
3116
t3.a=t2.a AND t3.c IN ('bb','ee') ;
3117
3117
id select_type table type possible_keys key key_len ref rows Extra
3118
3118
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
3119
1 SIMPLE t3 range PRIMARY,ci ci 7 NULL 6 Using where; Using index
3119
1 SIMPLE t3 range PRIMARY,ci ci 19 NULL 6 Using where; Using index
3120
3120
1 SIMPLE t2 ref si,ai ai 5 test.t3.a 1 Using where
3122
3122
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
3354
3354
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3355
3355
id select_type table type possible_keys key key_len ref rows Extra
3356
3356
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3357
1 SIMPLE t2 ref name name 6 test.t1.name 1
3357
1 SIMPLE t2 ref name name 15 test.t1.name 1
3358
3358
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3428
3428
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3429
3429
id select_type table type possible_keys key key_len ref rows Extra
3430
3430
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
3431
1 SIMPLE t2 ref name name 6 test.t1.name 1
3431
1 SIMPLE t2 ref name name 15 test.t1.name 1
3432
3432
SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
3556
3556
1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3557
3557
1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where
3559
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c2` = '?') and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3559
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3561
3561
Level Code Message
3562
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c2` = '?') and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3562
Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`
3564
3564
SELECT 1 AS ` `;