Below is the list of changes that have just been committed into a local
5.2 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-12-09 05:45:49+03:00, sergefp@stripped +23 -0
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method:
- Don't set HA_PART_KEY_FLAG for columns that may be NULL
- Make make_join_select() take into account that there may exist a
ref-or-null(const) without corresponding table condition (the condition
has been removed).
mysql-test/r/distinct.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/explain.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/heap.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/innodb_mysql.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/join.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/join_nested.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/join_outer_innodb.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/key_cache.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/myisam.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/ndb_basic.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/negation_elimination.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/null.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/null_key.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +7 -7
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/order_by.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/partition_pruning.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +3 -3
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/range.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +7 -7
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/select.result@stripped, 2006-12-09 05:45:44+03:00, sergefp@stripped +4 -4
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/select_safe.result@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/subselect.result@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +2 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/union.result@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +1 -1
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
mysql-test/r/view.result@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +5 -5
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method: test result update.
sql/sql_select.cc@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +3 -2
Make "t.maybe_null_key=expr [OR t.maybe_null_key IS NULL]" be removable
if it is guaranteed to be true by ref access method:
- Make make_join_select() take into account that there may exist a
ref-or-null(const) without corresponding table condition (the condition
has been removed).
sql/table.cc@stripped, 2006-12-09 05:45:45+03:00, sergefp@stripped +1 -11
Make "t.maybe_null_key=expr" be removable if it is guaranteed to be
true by ref access method:
- Don't set HA_PART_KEY_SEG for columns that may be NULL.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon.mylan
# Root: /home/psergey/mysql-5.2-part_key_seg-test
--- 1.440/sql/sql_select.cc 2006-12-09 05:45:56 +03:00
+++ 1.441/sql/sql_select.cc 2006-12-09 05:45:56 +03:00
@@ -5397,7 +5397,8 @@
}
}
- if (tmp || !cond)
+ if (tmp || !cond || tab->type == JT_REF || tab->type == JT_REF_OR_NULL ||
+ tab->type == JT_EQ_REF)
{
DBUG_EXECUTE("where",print_where(tmp,tab->table->alias););
SQL_SELECT *sel= tab->select= ((SQL_SELECT*)
@@ -5411,7 +5412,7 @@
The guard will turn the predicate on only after
the first match for outer tables is encountered.
*/
- if (cond)
+ if (cond && tmp)
{
/*
Because of QUICK_GROUP_MIN_MAX_SELECT there may be a select without
--- 1.246/sql/table.cc 2006-12-09 05:45:56 +03:00
+++ 1.247/sql/table.cc 2006-12-09 05:45:56 +03:00
@@ -1154,21 +1154,11 @@
share->table_name.str,
share->table_name.str);
share->crashed= 1; // Marker for CHECK TABLE
- goto to_be_deleted;
+ continue;
}
#endif
key_part->key_part_flag|= HA_PART_KEY_SEG;
}
-
- to_be_deleted:
-
- /*
- If the field can be NULL, don't optimize away the test
- key_part_column = expression from the WHERE clause
- as we need to test for NULL = NULL.
- */
- if (field->real_maybe_null())
- key_part->key_part_flag|= HA_PART_KEY_SEG;
}
keyinfo->usable_key_parts= usable_parts; // Filesort
--- 1.89/mysql-test/r/union.result 2006-12-09 05:45:56 +03:00
+++ 1.90/mysql-test/r/union.result 2006-12-09 05:45:56 +03:00
@@ -505,7 +505,7 @@
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1
-2 UNION t1 ref b b 5 const 1 Using where
+2 UNION t1 ref b b 5 const 1
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
drop table t1,t2;
create table t1 ( id int not null auto_increment, primary key (id) ,user_name text );
--- 1.10/mysql-test/r/innodb_mysql.result 2006-12-09 05:45:56 +03:00
+++ 1.11/mysql-test/r/innodb_mysql.result 2006-12-09 05:45:56 +03:00
@@ -335,7 +335,7 @@
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
-1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
+1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
--- 1.151/mysql-test/r/subselect.result 2006-12-09 05:45:56 +03:00
+++ 1.152/mysql-test/r/subselect.result 2006-12-09 05:45:56 +03:00
@@ -720,7 +720,7 @@
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -732,7 +732,7 @@
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
--- 1.178/mysql-test/r/view.result 2006-12-09 05:45:56 +03:00
+++ 1.179/mysql-test/r/view.result 2006-12-09 05:45:56 +03:00
@@ -2317,15 +2317,15 @@
CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a;
EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
-1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index
+1 SIMPLE t1 ref a a 5 const 1 Using index
+1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using index
EXPLAIN SELECT * FROM v1 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-1 PRIMARY t2 ref a a 10 const,test.t1.b 2 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 1 Using index
+1 PRIMARY t2 ref a a 10 const,test.t1.b 2 Using index
EXPLAIN SELECT * FROM v2 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 1 Using index
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3;
--- 1.24/mysql-test/r/join_nested.result 2006-12-09 05:45:56 +03:00
+++ 1.25/mysql-test/r/join_nested.result 2006-12-09 05:45:56 +03:00
@@ -1054,8 +1054,8 @@
(t8.b=t9.b OR t8.c IS NULL) AND
(t9.a=1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where
-1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using where
+1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00
+1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where
--- 1.45/mysql-test/r/ndb_basic.result 2006-12-09 05:45:56 +03:00
+++ 1.46/mysql-test/r/ndb_basic.result 2006-12-09 05:45:56 +03:00
@@ -568,7 +568,7 @@
insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2),(6,1),(7,1);
explain select * from t1 where a12345678901234567890123456789a1234567890=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a12345678901234567890123456789a1234567890 a12345678901234567890123456789a1234567890 5 const 1 Using where
+1 SIMPLE t1 ref a12345678901234567890123456789a1234567890 a12345678901234567890123456789a1234567890 5 const 1
select * from t1 where a12345678901234567890123456789a1234567890=2;
a1234567890123456789012345678901234567890 a12345678901234567890123456789a1234567890
5 2
--- 1.48/mysql-test/r/distinct.result 2006-12-09 05:45:56 +03:00
+++ 1.49/mysql-test/r/distinct.result 2006-12-09 05:45:56 +03:00
@@ -174,7 +174,7 @@
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary
-1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index
+1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index
1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
a
@@ -190,7 +190,7 @@
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
-1 SIMPLE t3 ref a a 5 test.t1.a 11 Using where; Using index; Distinct
+1 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct
select distinct t1.a from t1,t3 where t1.a=t3.a;
a
1
--- 1.19/mysql-test/r/explain.result 2006-12-09 05:45:56 +03:00
+++ 1.20/mysql-test/r/explain.result 2006-12-09 05:45:56 +03:00
@@ -50,7 +50,7 @@
id select_type table type possible_keys key key_len ref rows Extra
set names latin1;
select 3 into @v1;
--- 1.51/mysql-test/r/heap.result 2006-12-09 05:45:56 +03:00
+++ 1.52/mysql-test/r/heap.result 2006-12-09 05:45:56 +03:00
@@ -204,7 +204,7 @@
INSERT INTO t1 VALUES (10), (10), (10);
EXPLAIN SELECT * FROM t1 WHERE a=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 3 Using where
+1 SIMPLE t1 ref a a 5 const 3
SELECT * FROM t1 WHERE a=10;
a
10
--- 1.40/mysql-test/r/join.result 2006-12-09 05:45:56 +03:00
+++ 1.41/mysql-test/r/join.result 2006-12-09 05:45:56 +03:00
@@ -774,5 +774,5 @@
explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL a,b NULL NULL NULL 1000 Using where
-1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where
+1 SIMPLE t3 ref b b 5 test.t2.b 1
drop table t1, t2, t3;
--- 1.38/mysql-test/r/null.result 2006-12-09 05:45:56 +03:00
+++ 1.39/mysql-test/r/null.result 2006-12-09 05:45:56 +03:00
@@ -170,7 +170,7 @@
insert into t1 values(null);
explain select * from t1 where i=2 or i is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index
+1 SIMPLE t1 ref_or_null i i 5 const 9 Using index
select count(*) from t1 where i=2 or i is null;
count(*)
10
--- 1.37/mysql-test/r/null_key.result 2006-12-09 05:45:56 +03:00
+++ 1.38/mysql-test/r/null_key.result 2006-12-09 05:45:56 +03:00
@@ -21,10 +21,10 @@
1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
explain select * from t1 where (a is null or a = 7) and b=7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index
explain select * from t1 where (a is null or a = 7) and b=7 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index
@@ -151,7 +151,7 @@
insert into t1 values (7,null), (8,null), (8,7);
explain select * from t1 where a = 7 and (b=7 or b is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index
+1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index
select * from t1 where a = 7 and (b=7 or b is null);
a b
7 7
@@ -166,7 +166,7 @@
NULL 7
explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index
+1 SIMPLE t1 ref_or_null a a 5 const 5 Using index
select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
a b
7 NULL
@@ -192,7 +192,7 @@
explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index
select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
a a b
7 7 7
@@ -202,7 +202,7 @@
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
a a b
7 7 7
@@ -226,7 +226,7 @@
explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
-1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
+1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index
explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
--- 1.55/mysql-test/r/order_by.result 2006-12-09 05:45:56 +03:00
+++ 1.56/mysql-test/r/order_by.result 2006-12-09 05:45:56 +03:00
@@ -638,7 +638,7 @@
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -647,7 +647,7 @@
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
--- 1.48/mysql-test/r/range.result 2006-12-09 05:45:56 +03:00
+++ 1.49/mysql-test/r/range.result 2006-12-09 05:45:56 +03:00
@@ -220,31 +220,31 @@
update t1 set y=x;
explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 2 Using where
explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 2 Using where
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 3 Using where
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 3 Using where
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 2 Using where
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref y y 5 const 1 Using where
+1 SIMPLE t1 ref y y 5 const 1
1 SIMPLE t2 range x x 5 NULL 2 Using where
explain select count(*) from t1 where x in (1);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref x x 5 const 1 Using where; Using index
+1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
--- 1.135/mysql-test/r/select.result 2006-12-09 05:45:56 +03:00
+++ 1.136/mysql-test/r/select.result 2006-12-09 05:45:56 +03:00
@@ -2716,7 +2716,7 @@
where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and
t2.b like '%%' order by t2.b limit 0,1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
@@ -3443,12 +3443,12 @@
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
+1 SIMPLE t2 ref c c 5 test.t1.a 2
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using where
+1 SIMPLE t2 ref c c 5 test.t1.a 2
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3508,7 +3508,7 @@
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 const idx1 NULL NULL NULL 1
-1 SIMPLE t3 ref idx1 idx1 5 const 3 Using where
+1 SIMPLE t3 ref idx1 idx1 5 const 3
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
WHERE t1.id=2;
id a b c d e
--- 1.17/mysql-test/r/select_safe.result 2006-12-09 05:45:56 +03:00
+++ 1.18/mysql-test/r/select_safe.result 2006-12-09 05:45:56 +03:00
@@ -67,12 +67,12 @@
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 21
-1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where
+1 SIMPLE t2 ref b b 21 test.t1.b 6
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 21
-1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where
+1 SIMPLE t2 ref b b 21 test.t1.b 6
SET MAX_SEEKS_FOR_KEY=DEFAULT;
drop table t1;
create table t1 (a int);
--- 1.89/mysql-test/r/myisam.result 2006-12-09 05:45:56 +03:00
+++ 1.90/mysql-test/r/myisam.result 2006-12-09 05:45:56 +03:00
@@ -358,7 +358,7 @@
explain select * from t1,t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL b NULL NULL NULL 2
-1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where
+1 SIMPLE t1 ref b b 5 test.t2.b 1
explain select * from t1,t2 force index(c) where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
@@ -371,7 +371,7 @@
1 SIMPLE t1 range a a 4 NULL 4 Using where
explain select * from t1 where c=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c,c_2 c 5 const 1 Using where
+1 SIMPLE t1 ref c,c_2 c 5 const 1
explain select * from t1 use index() where c=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
--- 1.20/mysql-test/r/key_cache.result 2006-12-09 05:45:56 +03:00
+++ 1.21/mysql-test/r/key_cache.result 2006-12-09 05:45:56 +03:00
@@ -134,7 +134,7 @@
explain select count(*) from t1, t2 where t1.p = t2.i;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index
-1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using where; Using index
+1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using index
select count(*) from t1, t2 where t1.p = t2.i;
count(*)
3
--- 1.2/mysql-test/r/join_outer_innodb.result 2006-12-09 05:45:56 +03:00
+++ 1.3/mysql-test/r/join_outer_innodb.result 2006-12-09 05:45:56 +03:00
@@ -9,7 +9,7 @@
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
-1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
+1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
--- 1.7/mysql-test/r/negation_elimination.result 2006-12-09 05:45:56 +03:00
+++ 1.8/mysql-test/r/negation_elimination.result 2006-12-09 05:45:56 +03:00
@@ -79,7 +79,7 @@
19
explain select * from t1 where not(a != 10);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
+1 SIMPLE t1 ref a a 5 const 1 Using index
select * from t1 where not(a != 1);
a
1
--- 1.21/mysql-test/r/partition_pruning.result 2006-12-09 05:45:56 +03:00
+++ 1.22/mysql-test/r/partition_pruning.result 2006-12-09 05:45:56 +03:00
@@ -537,15 +537,15 @@
1 SIMPLE t2 p0,p1,p2,p3 ALL NULL NULL NULL NULL 710 Using where
explain partitions select * from t2 where b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76
explain extended select * from t2 where b = 6;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ref b b 5 const 76 100.00 Using where
+1 SIMPLE t2 ref b b 5 const 76 100.00
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
explain partitions select * from t2 where b = 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76
explain extended select * from t2 where b in (1,3,5);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where
| Thread |
|---|
| • bk commit into 5.2 tree (sergefp:1.2334) | Sergey Petrunia | 9 Dec |
