diff options
Diffstat (limited to 'mysql-test/main/select_jcl6.result')
| -rw-r--r-- | mysql-test/main/select_jcl6.result | 32 |
1 files changed, 20 insertions, 12 deletions
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 51d652fa245..b93ba17e174 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -1391,16 +1391,16 @@ id select_type table type possible_keys key key_len ref rows Extra delete from t2 where fld1=999999; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where @@ -3608,7 +3608,7 @@ CREATE TABLE t1(id int PRIMARY KEY, b int, e int); CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); INSERT INTO t1 VALUES -(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), +(1,10,19), (2,20,22), (4,41,42), (9,39,95), (7, 77,79), (6,63,67), (5,55,58), (3,38,39), (8,81,89); INSERT INTO t2 VALUES (21,210), (41,410), (82,820), (83,830), (84,840), @@ -3636,6 +3636,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan 1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +EXPLAIN +SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 +WHERE t1.id = 9 AND t2.i BETWEEN t1.b AND t1.e AND +t3.a=t2.a AND t3.c IN ('bb','ee'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t3 range PRIMARY,ci ci 5 NULL 6 Using index condition; Rowid-ordered scan +1 SIMPLE t2 hash_range si #hash#$hj:si 5:5 test.t3.a 13 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3728,7 +3736,7 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra @@ -3742,7 +3750,7 @@ CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra @@ -3755,7 +3763,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); @@ -3776,7 +3784,7 @@ AND t1.ts BETWEEN t2.dt1 AND t2.dt2 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range ts ts 4 NULL 2 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 ALL ts NULL NULL NULL 2 Using where SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 AND t1.ts BETWEEN t2.dt1 AND t2.dt2 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; @@ -4042,7 +4050,7 @@ EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 ON ( f1.b=f2.b AND f1.a<f2.a ) WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE f1 range inx inx 5 NULL 7 Using where; Using index +1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index DROP TABLE t1; CREATE TABLE t1 (c1 INT, c2 INT); |