diff options
40 files changed, 324 insertions, 323 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index cb3adfb459d..96abcd34b92 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -104,7 +104,7 @@ insert into t1 (b) values ("hello"),("my"),("world"); create table t2 (key (b)) select * from t1; explain select * from t2 where b="world"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref B B 21 const 1 Using where +1 SIMPLE t2 ref B B 21 const 1 Using index condition select * from t2 where b="world"; a B 3 world diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index b42094550bd..f5c17ebfdf8 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -566,31 +566,31 @@ INSERT INTO t1 VALUES ('i','i'); INSERT INTO t1 VALUES ('j','j'); EXPLAIN SELECT * FROM t1 WHERE s1='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s1 s1 11 const 1 Using where +1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s2 s2 11 const 1 Using where +1 SIMPLE t1 ref s2 s2 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref s1 s1 11 const 1 Using where +1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 2 Using where +1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 2 Using where +1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range s1 s1 11 NULL 1 Using where +1 SIMPLE t1 range s1 s1 11 NULL 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a871e1ae761..ab48ccdcf15 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -13,7 +13,7 @@ id str 3 foo explain select * from t1 where str is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref str str 11 const 1 Using where +1 SIMPLE t1 ref str str 11 const 1 Using index condition explain select * from t1 where str="foo"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const str str 11 const 1 diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 779049f8b4c..650f103710d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -241,7 +241,7 @@ insert into t2 select C.a*2+1, 'yes' from t1 C; explain select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 12 Using where +1 SIMPLE t2 range a a 5 NULL 12 Using index condition select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); a filler 1 yes @@ -256,10 +256,10 @@ a filler 19 yes explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 912 Using where +1 SIMPLE t2 range a a 5 NULL 912 Using index condition explain select * from t2 force index(a) where a <> 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 912 Using where +1 SIMPLE t2 range a a 5 NULL 912 Using index condition drop table t2; create table t2 (a datetime, filler char(200), key(a)); insert into t2 select '2006-04-25 10:00:00' + interval C.a minute, @@ -271,7 +271,7 @@ select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 9 NULL 18 Using where +1 SIMPLE t2 range a a 9 NULL 18 Using index condition select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); @@ -295,7 +295,7 @@ insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), ('barbas','1'), ('bazbazbay', '1'),('zz','1'); explain select * from t2 where a not in('foo','barbar', 'bazbazbaz'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 13 NULL 7 Using where +1 SIMPLE t2 range a a 13 NULL 7 Using index condition drop table t2; create table t2 (a decimal(10,5), filler char(200), key(a)); insert into t2 select 345.67890, 'no' from t1 A, t1 B; @@ -306,7 +306,7 @@ insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), explain select * from t2 where a not in (345.67890, 43245.34, 64224.56344); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 7 NULL 7 Using where +1 SIMPLE t2 range a a 7 NULL 7 Using index condition select * from t2 where a not in (345.67890, 43245.34, 64224.56344); a filler 0.00000 1 @@ -630,16 +630,16 @@ INSERT INTO t1 (c_int) SELECT 0 FROM t1; INSERT INTO t1 (c_int) SELECT 0 FROM t1; EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -648,10 +648,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -660,10 +660,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -672,10 +672,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -685,11 +685,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_date IN ('2009-09-01', '2009-09-02', '2009-09-03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -699,11 +699,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_datetime IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -713,11 +713,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -726,10 +726,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -738,10 +738,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 8bb3ad9c250..0572aa9e297 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1898,8 +1898,8 @@ SELECT a, AVG(t1.b), FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 10 NULL 9 Using index -3 DEPENDENT SUBQUERY t12 ref a a 10 func,func 2 Using where -2 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using where +3 DEPENDENT SUBQUERY t12 ref a a 10 func,func 2 Using index condition +2 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using index condition SELECT a, AVG(t1.b), (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result index e38828dcf89..d36580d3764 100644 --- a/mysql-test/r/index_intersect.result +++ b/mysql-test/r/index_intersect.result @@ -74,12 +74,12 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 300000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Name 35 NULL # Using where +1 SIMPLE City range Population,Name Name 35 NULL # Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 7000000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Population 4 NULL # Using where +1 SIMPLE City range Population,Name Population 4 NULL # Using index condition; Using where SELECT * FROM City USE INDEX () WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -371,7 +371,7 @@ EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name,Country Name # NULL # Using where +1 SIMPLE City range Population,Name,Country Name # NULL # Using index condition; Using where SELECT * FROM City USE INDEX () WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; ID Name Country Population @@ -462,7 +462,7 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using where +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; @@ -472,7 +472,7 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Country 3 NULL # Using where +1 SIMPLE City range PRIMARY,Population,Country Country 3 NULL # Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 @@ -484,7 +484,7 @@ SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using where +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population @@ -733,7 +733,7 @@ EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Name 35 NULL # Using where +1 SIMPLE City range Population,Country,Name Name 35 NULL # Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; @@ -744,7 +744,7 @@ SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using where +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -1035,7 +1035,7 @@ EXPLAIN SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,f4 f4 35 NULL # Using where +1 SIMPLE t1 range PRIMARY,f4 f4 35 NULL # Using index condition; Using where SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; f1 f4 f5 diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index b484afca73b..773e74f9701 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -549,7 +549,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using index condition; Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index ea578accaaa..1f35fb7a16e 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -21,7 +21,7 @@ Table Op Msg_type Msg_text test.t0 analyze status OK explain select * from t0 where key1 < 3 or key1 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 78 Using where +1 SIMPLE t0 range i1 i1 4 NULL 78 Using index condition; Using where explain select * from t0 where key1 < 3 or key2 > 1020; id select_type table type possible_keys key key_len ref rows Extra @@ -277,7 +277,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 3 Using where +1 SIMPLE t0 range i1 i1 4 NULL 3 Using index condition 1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81) explain select * from t1 where key1=3 or key2=4 union select * from t1 where key1<4 or key3=5; @@ -1379,7 +1379,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index bd0df96c518..d7cd1491796 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1264,7 +1264,7 @@ SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 -Handler_read_next 2 +Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 1 diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 234ac4a88d9..fb4ebb3c4b7 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1457,12 +1457,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X Using where 1 SIMPLE t5 ref a a 5 test.t3.b X -1 SIMPLE t4 ref a a 5 test.t3.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition 1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X 1 SIMPLE t5 ref a a 5 test.t2.b X Using where diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 97bb066a32e..7b3ded8d050 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -628,7 +628,7 @@ select p from t1 where p between 1010 and 1020; p explain select i from t2 where p between 1010 and 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 28 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 28 Using index condition select i from t2 where p between 1010 and 1020; i 1 diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 289be98c1b5..5fcb51eb345 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -673,7 +673,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using where +1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using index condition EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e0a3d1af089..29a8ef7abe8 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -1114,11 +1114,11 @@ count(*) 29267 explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM; insert into t1 (id) values (null),(null),(null),(null),(null); @@ -1559,7 +1559,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1735,7 +1735,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index e02c57ee8b9..9fa51eda142 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -392,7 +392,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 4 Using index condition; 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 @@ -1254,7 +1254,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1430,7 +1430,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 1cdc48e6552..ac72e1e303e 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -148,10 +148,10 @@ insert into t1 values (7,7), (8,8), (9,9), (10,10), (11,11), (12,12); explain select * from t1 where a between 2 and 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 2 Using where +1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition explain select * from t1 where a between 2 and 3 or b is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 2 Using where +1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition drop table t1; select cast(NULL as signed); cast(NULL as signed) diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 78e28c75ab1..488110b88e3 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -76,13 +76,13 @@ insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b = 2 and c=0; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b = 7 and c=0; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a=2 and b = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 1 Using where @@ -91,25 +91,25 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 5 Using where +1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using where +1 SIMPLE t1 range a a 5 NULL 1 Using index condition explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 4 Using where +1 SIMPLE t1 range a,b a 5 NULL 4 Using index condition; Using where explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using where +1 SIMPLE t1 range a a 5 NULL 1 Using index condition explain select * from t1 where b like "6%"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 12 NULL 1 Using where @@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result index 9286e8687db..e49b93548c8 100644 --- a/mysql-test/r/optimizer_switch.result +++ b/mysql-test/r/optimizer_switch.result @@ -4,19 +4,19 @@ # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -43,60 +43,60 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off BUG#37120 optimizer_switch allowable values not according to specification select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off set optimizer_switch=default; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 5d6b2a18003..f4aa929c01e 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -515,7 +515,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition drop table t1,t2,t3; CREATE TABLE t1 ( `titre` char(80) NOT NULL default '', @@ -612,10 +612,10 @@ DS-MRR: use two IGNORE INDEX queries, otherwise we get cost races, because DS-MRR: records_in_range/read_time return the same numbers for all three indexes EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using where; Using filesort +1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 @@ -645,7 +645,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12); 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 4 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -654,7 +654,7 @@ a b 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 3 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -1114,7 +1114,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using filesort SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 @@ -1491,8 +1491,8 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort -1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where +1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; @@ -1627,19 +1627,19 @@ INSERT INTO t2 SELECT a+4, b FROM t2; EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) # should have "using filesort" EXPLAIN SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) # should have "using filesort" EXPLAIN SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) DROP TABLE t1, t2; # diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 8d578794d6d..924a0105367 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -465,9 +465,9 @@ def key 253 64 7 Y 0 31 8 def key_len 253 4096 1 Y 0 31 8 def ref 253 2048 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 -def Extra 253 255 27 N 1 31 8 +def Extra 253 255 37 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort drop table if exists t2; create table t2 (id smallint, name varchar(20)) ; prepare stmt1 from ' insert into t2 values(?, ?) ' ; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 6155a0ee433..09da355be1c 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -221,27 +221,27 @@ 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 -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join) 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 -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join) 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 -1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (flat, BNL join) 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 -1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (flat, BNL join) 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 -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join) 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 -1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (flat, BNL join) 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 index @@ -276,7 +276,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 2 Using where +1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; @@ -421,19 +421,19 @@ test.t1 analyze status OK test.t2 analyze status Table is already up to date explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where +1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition 1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; id name uid id name uid @@ -615,13 +615,13 @@ INSERT INTO t1 (a) VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from t1 where a='aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 11 const 2 Using where +1 SIMPLE t1 ref a a 11 const 2 Using index condition explain select * from t1 where a=binary 'aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 11 NULL 2 Using where +1 SIMPLE t1 range a a 11 NULL 2 Using index condition explain select * from t1 where a='aaa' collate latin1_bin; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 11 NULL 2 Using where +1 SIMPLE t1 range a a 11 NULL 2 Using index condition explain select * from t1 where a='aaa' collate latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 9 Using where @@ -716,7 +716,7 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where +1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition 1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND @@ -890,10 +890,10 @@ INSERT INTO t1 VALUES (55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C'); EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 11 Using where +1 SIMPLE t1 range status status 23 NULL 11 Using index condition EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 11 Using where +1 SIMPLE t1 range status status 23 NULL 11 Using index condition SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; id status 53 C @@ -922,10 +922,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range status status 23 NULL 11 Using where; Using index EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 10 Using where +1 SIMPLE t1 range status status 23 NULL 10 Using index condition EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 10 Using where +1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id status 53 C @@ -1026,20 +1026,20 @@ create table t2 (a varchar(10), filler char(200), key(a)); insert into t2 select * from t1; explain select * from t1 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition explain select * from t1 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 ref a a 13 const # Using index condition; Using where update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 @@ -1083,7 +1083,7 @@ id b c 0 3 4 EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using index condition; Using where EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where @@ -1115,7 +1115,7 @@ INSERT INTO t1 VALUES ('A2','2005-12-01 08:00:00',1000); EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where +1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: @@ -1153,7 +1153,7 @@ INSERT INTO t1 VALUES This must use range access: explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range dateval dateval 4 NULL 2 Using where +1 SIMPLE t1 range dateval dateval 4 NULL 2 Using index condition drop table t1; CREATE TABLE t1 ( a varchar(32), index (a) @@ -1219,7 +1219,7 @@ Z In following EXPLAIN the access method should be ref, #rows~=500 (and not 2) explain select * from t2 where a=1000 and b<11; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 5 const 502 Using where +1 SIMPLE t2 ref a a 5 const 502 Using index condition drop table t1, t2; CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); @@ -1690,7 +1690,7 @@ pk i4 EXPLAIN SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using index condition SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; pk i4 1 10 @@ -1699,7 +1699,7 @@ pk i4 EXPLAIN SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; pk i4 1 10 @@ -1733,7 +1733,7 @@ pk i4 EXPLAIN SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using index condition SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; pk i4 1 10 @@ -1748,7 +1748,7 @@ pk i4 EXPLAIN SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; pk i4 1 10 @@ -1757,14 +1757,14 @@ EXPLAIN SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; pk i4 pk i4 EXPLAIN SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; pk i4 pk i4 DROP TABLE t1; @@ -1793,7 +1793,7 @@ INSERT INTO t100(I,J) VALUES(8,26); EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range I I 10 NULL 4 Using where +1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); K I J diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 933da379640..f8738457dd9 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -49,14 +49,14 @@ SELECT * FROM City WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR (Population < 100000 OR Name Like 'T%') AND Country='ARG'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Country 3 NULL 104 Using where +1 SIMPLE City range Population,Country,Name Country 3 NULL 104 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Population < 200000 AND Name LIKE 'P%' AND (Population > 300000 OR Name LIKE 'T%') AND (Population < 100000 OR Name LIKE 'Pa%'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Name 35 NULL 135 Using where +1 SIMPLE City range Population,Name Name 35 NULL 135 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Population > 100000 AND Name LIKE 'Aba%' OR @@ -69,12 +69,12 @@ EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 459 Using where +1 SIMPLE City range Population Population 4 NULL 459 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 39 Using where +1 SIMPLE City range Population Population 4 NULL 39 Using index condition EXPLAIN SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')); @@ -91,7 +91,7 @@ SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) AND (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Population 4 NULL 39 Using where +1 SIMPLE City range Population,Country,Name Population 4 NULL 39 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) AND (Population > 101000 AND Population < 115000); @@ -171,37 +171,37 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (Name < 'Ac'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 13 Using where +1 SIMPLE City range Name Name 35 NULL 13 Using index condition EXPLAIN SELECT * FROM City WHERE (Name < 'Bb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 208 Using where +1 SIMPLE City range Name Name 35 NULL 208 Using index condition EXPLAIN SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 104 Using where +1 SIMPLE City range Country Country 3 NULL 104 Using index condition EXPLAIN SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'Pb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 39 Using where +1 SIMPLE City range Name Name 35 NULL 39 Using index condition EXPLAIN SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 221 Using where +1 SIMPLE City range Name Name 35 NULL 221 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 328 Using where +1 SIMPLE City range Population Population 4 NULL 328 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 103000 AND Population < 104000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 37 Using where +1 SIMPLE City range Population Population 4 NULL 37 Using index condition EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Name 35 NULL 52 Using where +1 SIMPLE City range Population,Country,Name Name 35 NULL 52 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR @@ -327,11 +327,11 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra @@ -339,22 +339,22 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 19 Using where +1 SIMPLE City range Country Country 3 NULL 19 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 222 Using where +1 SIMPLE City range Name Name 35 NULL 222 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 72 Using where +1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 21 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 21 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -576,39 +576,39 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE Population > 101000 AND Population < 102000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 39 Using where +1 SIMPLE City range Population Population 4 NULL 39 Using index condition EXPLAIN SELECT * FROM City WHERE Population > 101000 AND Population < 110000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 328 Using where +1 SIMPLE City range Population Population 4 NULL 328 Using index condition EXPLAIN SELECT * FROM City WHERE Country < 'C'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 436 Using where +1 SIMPLE City range Country Country 3 NULL 436 Using index condition EXPLAIN SELECT * FROM City WHERE Country < 'AGO'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 6 Using where +1 SIMPLE City range Country Country 3 NULL 6 Using index condition EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 221 Using where +1 SIMPLE City range Name Name 35 NULL 221 Using index condition EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 39 Using where +1 SIMPLE City range Name Name 35 NULL 39 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 401 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 401 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 135 Using where +1 SIMPLE City range Name Name 35 NULL 135 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND @@ -679,23 +679,23 @@ CREATE INDEX CountryPopulation ON City(Country,Population); EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pas%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 5 Using where +1 SIMPLE City range Name Name 35 NULL 5 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 135 Using where +1 SIMPLE City range Name Name 35 NULL 135 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 81 Using where +1 SIMPLE City range Population Population 4 NULL 81 Using index condition EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation Country 3 const 267 Using where +1 SIMPLE City ref Country,CountryPopulation Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation Country 3 const 6 Using where +1 SIMPLE City ref Country,CountryPopulation Country 3 const 6 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') @@ -707,7 +707,7 @@ SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') AND Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 6 Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 6 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -752,51 +752,51 @@ CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 267 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition EXPLAIN SELECT * FROM City WHERE Country='BRA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 221 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 221 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 301 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 301 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 250 and 260 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 39 Using where +1 SIMPLE City range Population Population 4 NULL 39 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 81 Using where +1 SIMPLE City range Population Population 4 NULL 81 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 41 Using where +1 SIMPLE City range Name Name 35 NULL 41 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -817,7 +817,7 @@ WHERE ((Population > 101000 AND Population < 110000) OR ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 3 const 5 Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 3 const 5 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' @@ -949,7 +949,7 @@ WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 23 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 23 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 11000) OR @@ -1421,7 +1421,7 @@ SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using where +1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 166177a9ccc..35e4c04f30d 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -50,14 +50,14 @@ SELECT * FROM City WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR (Population < 100000 OR Name Like 'T%') AND Country='ARG'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Country 3 NULL 106 Using where +1 SIMPLE City range Population,Country,Name Country 3 NULL 106 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Population < 200000 AND Name LIKE 'P%' AND (Population > 300000 OR Name LIKE 'T%') AND (Population < 100000 OR Name LIKE 'Pa%'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Name 35 NULL 235 Using where +1 SIMPLE City range Population,Name Name 35 NULL 235 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Population > 100000 AND Name LIKE 'Aba%' OR @@ -70,12 +70,12 @@ EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 458 Using where +1 SIMPLE City range Population Population 4 NULL 458 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 38 Using where +1 SIMPLE City range Population Population 4 NULL 38 Using index condition EXPLAIN SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')); @@ -92,7 +92,7 @@ SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) AND (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Population 4 NULL 38 Using where +1 SIMPLE City range Population,Country,Name Population 4 NULL 38 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) AND (Population > 101000 AND Population < 115000); @@ -172,37 +172,37 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (Name < 'Ac'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 23 Using where +1 SIMPLE City range Name Name 35 NULL 23 Using index condition EXPLAIN SELECT * FROM City WHERE (Name < 'Bb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 373 Using where +1 SIMPLE City range Name Name 35 NULL 373 Using index condition EXPLAIN SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 106 Using where +1 SIMPLE City range Country Country 3 NULL 106 Using index condition EXPLAIN SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'Pb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 71 Using where +1 SIMPLE City range Name Name 35 NULL 71 Using index condition EXPLAIN SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 384 Using where +1 SIMPLE City range Name Name 35 NULL 384 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 327 Using where +1 SIMPLE City range Population Population 4 NULL 327 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 103000 AND Population < 104000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 36 Using where +1 SIMPLE City range Population Population 4 NULL 36 Using index condition EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Name 35 NULL 94 Using where +1 SIMPLE City range Population,Country,Name Name 35 NULL 94 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR @@ -328,34 +328,34 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 2006 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 2006 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 19 Using where +1 SIMPLE City range Country Country 3 NULL 19 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 394 Using where +1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 133 Using where +1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND @@ -577,39 +577,39 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE Population > 101000 AND Population < 102000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 38 Using where +1 SIMPLE City range Population Population 4 NULL 38 Using index condition EXPLAIN SELECT * FROM City WHERE Population > 101000 AND Population < 110000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 327 Using where +1 SIMPLE City range Population Population 4 NULL 327 Using index condition EXPLAIN SELECT * FROM City WHERE Country < 'C'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 446 Using where +1 SIMPLE City range Country Country 3 NULL 446 Using index condition EXPLAIN SELECT * FROM City WHERE Country < 'AGO'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country Country 3 NULL 5 Using where +1 SIMPLE City range Country Country 3 NULL 5 Using index condition EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 384 Using where +1 SIMPLE City range Name Name 35 NULL 384 Using index condition EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 71 Using where +1 SIMPLE City range Name Name 35 NULL 71 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 944 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 944 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 235 Using where +1 SIMPLE City range Name Name 35 NULL 235 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND @@ -680,23 +680,23 @@ CREATE INDEX CountryPopulation ON City(Country,Population); EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pas%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 8 Using where +1 SIMPLE City range Name Name 35 NULL 8 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 235 Using where +1 SIMPLE City range Name Name 35 NULL 235 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 80 Using where +1 SIMPLE City range Population Population 4 NULL 80 Using index condition EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation Country 3 const 274 Using where +1 SIMPLE City ref Country,CountryPopulation Country 3 const 274 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using where +1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') @@ -708,7 +708,7 @@ SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') AND Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -753,51 +753,51 @@ CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 274 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 274 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition EXPLAIN SELECT * FROM City WHERE Country='BRA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 250 Using where +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 250 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition EXPLAIN SELECT * FROM City WHERE ID BETWEEN 250 and 260 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 38 Using where +1 SIMPLE City range Population Population 4 NULL 38 Using index condition EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 80 Using where +1 SIMPLE City range Population Population 4 NULL 80 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 71 Using where +1 SIMPLE City range Name Name 35 NULL 71 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -818,7 +818,7 @@ WHERE ((Population > 101000 AND Population < 110000) OR ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' @@ -950,14 +950,14 @@ WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition; Using where EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName Name 35 NULL 1 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName Name 35 NULL 1 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' @@ -1422,7 +1422,7 @@ SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using where +1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); @@ -1434,7 +1434,7 @@ EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 1 Using where +1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 1 Using index condition; Using where SELECT * FROM t1 WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; a b diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index fde22cb20fb..57043b27660 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3418,7 +3418,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 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 PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where DROP TABLE t1,t2; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); INSERT t1 SET i = 0; @@ -3454,7 +3454,7 @@ In next EXPLAIN, B.rows must be exactly 10: explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where +1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where 1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 drop table t1, t2; CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); @@ -3468,12 +3468,12 @@ INSERT INTO t2 VALUES EXPLAIN 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where 1 SIMPLE t2 ref c c 5 test.t1.a 2 DROP TABLE t1, t2; create table t1 ( @@ -3563,19 +3563,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3609,7 +3609,7 @@ WHERE t1.id = 8 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 t2 range si si 5 NULL 4 Using where +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3617,7 +3617,7 @@ WHERE t1.id = 8 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 t2 range si,ai si 5 NULL 4 Using where +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3625,7 +3625,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=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 t2 range si si 5 NULL 2 Using where +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3633,7 +3633,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=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 t2 range si,ai si 5 NULL 2 Using where +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); @@ -3753,7 +3753,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 1 Using where +1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; 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"; @@ -4378,12 +4378,12 @@ CREATE TABLE t1 (a INT KEY, b INT); INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 DROP TABLE t1; diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index fde22cb20fb..57043b27660 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -3418,7 +3418,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 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 PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where DROP TABLE t1,t2; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); INSERT t1 SET i = 0; @@ -3454,7 +3454,7 @@ In next EXPLAIN, B.rows must be exactly 10: explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where +1 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where 1 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 drop table t1, t2; CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); @@ -3468,12 +3468,12 @@ INSERT INTO t2 VALUES EXPLAIN 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where 1 SIMPLE t2 ref c c 5 test.t1.a 2 DROP TABLE t1, t2; create table t1 ( @@ -3563,19 +3563,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3609,7 +3609,7 @@ WHERE t1.id = 8 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 t2 range si si 5 NULL 4 Using where +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3617,7 +3617,7 @@ WHERE t1.id = 8 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 t2 range si,ai si 5 NULL 4 Using where +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3625,7 +3625,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=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 t2 range si si 5 NULL 2 Using where +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3633,7 +3633,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=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 t2 range si,ai si 5 NULL 2 Using where +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); @@ -3753,7 +3753,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 1 Using where +1 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; 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"; @@ -4378,12 +4378,12 @@ CREATE TABLE t1 (a INT KEY, b INT); INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index be922e7cbd4..8368385efd2 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1136,7 +1136,7 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; LooseScan +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) 1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) drop table t1, t3, t4; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index d155a2cd3a8..499963710e1 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1847,7 +1847,7 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk 2 diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 287fae523fe..62038178012 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -347,7 +347,7 @@ AND a = SOME (SELECT b FROM t5)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; LooseScan -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using where +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using index condition; Using where SELECT * FROM t3 WHERE t3.b > ALL ( diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 892ac29d6d5..4b4ea83fcce 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -128,7 +128,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 @@ -136,7 +136,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v1 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: @@ -156,7 +156,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 @@ -164,7 +164,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 345da7a0163..f28a53acc0e 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -778,7 +778,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); @@ -1219,7 +1219,7 @@ count(*) 623 explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # Using index condition update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra @@ -1914,7 +1914,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -2090,7 +2090,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index bd8760b8f79..e8ba522af2e 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -119,7 +119,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL @@ -323,7 +323,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 1bd572d7231..6c22359e176 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2725,7 +2725,7 @@ WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 HAVING f > 7 ORDER BY f; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort 1 SIMPLE t2 ref idx idx 5 test.t1.pk 1 Using index SELECT t1 .i AS f FROM t1, t2 WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 @@ -2757,7 +2757,7 @@ SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort 1 SIMPLE t2 ref a a 5 test.t1.pk 1 Using index SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 diff --git a/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result index bd8760b8f79..e8ba522af2e 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result @@ -119,7 +119,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL @@ -323,7 +323,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL diff --git a/mysql-test/suite/maria/r/maria.result b/mysql-test/suite/maria/r/maria.result index 4ff0b57a230..e6835a478f7 100644 --- a/mysql-test/suite/maria/r/maria.result +++ b/mysql-test/suite/maria/r/maria.result @@ -401,10 +401,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 5 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using index condition; Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 5 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using index condition; 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 2 @@ -1143,7 +1143,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1319,7 +1319,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result index 7c9a278b581..bef8c92419a 100644 --- a/mysql-test/suite/pbxt/r/null_key.result +++ b/mysql-test/suite/pbxt/r/null_key.result @@ -76,13 +76,13 @@ insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b = 2 and c=0; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b = 7 and c=0; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a=2 and b = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 1 Using where @@ -91,25 +91,25 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 5 Using where +1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 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 +1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using where +1 SIMPLE t1 range a a 5 NULL 1 Using index condition explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 4 Using where +1 SIMPLE t1 range a,b a 5 NULL 4 Using index condition; Using where explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using where +1 SIMPLE t1 range a a 5 NULL 1 Using index condition explain select * from t1 where b like "6%"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 12 NULL 1 Using where @@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 diff --git a/mysql-test/suite/pbxt/r/order_by.result b/mysql-test/suite/pbxt/r/order_by.result index 4fc7abec96c..4f56f433169 100644 --- a/mysql-test/suite/pbxt/r/order_by.result +++ b/mysql-test/suite/pbxt/r/order_by.result @@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition drop table t1,t2,t3; CREATE TABLE t1 ( `titre` char(80) NOT NULL default '', diff --git a/mysql-test/suite/pbxt/r/ps_1general.result b/mysql-test/suite/pbxt/r/ps_1general.result index baa944eebab..2f4fa8200c2 100644 --- a/mysql-test/suite/pbxt/r/ps_1general.result +++ b/mysql-test/suite/pbxt/r/ps_1general.result @@ -465,9 +465,9 @@ def key 253 64 7 Y 0 31 8 def key_len 253 4096 1 Y 0 31 8 def ref 253 2048 0 Y 0 31 8 def rows 8 10 1 Y 32928 0 63 -def Extra 253 255 27 N 1 31 8 +def Extra 253 255 37 N 1 31 8 id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort drop table if exists t2; create table t2 (id smallint, name varchar(20)) ; prepare stmt1 from ' insert into t2 values(?, ?) ' ; diff --git a/mysql-test/suite/pbxt/r/range.result b/mysql-test/suite/pbxt/r/range.result index 368fd6b946c..3d512626a73 100644 --- a/mysql-test/suite/pbxt/r/range.result +++ b/mysql-test/suite/pbxt/r/range.result @@ -277,7 +277,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 2 Using where +1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; @@ -922,7 +922,7 @@ INSERT INTO t1 VALUES ('A2','2005-12-01 08:00:00',1000); EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where +1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index fb63c6e6739..a0ccc166199 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -44,7 +44,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -141,7 +141,7 @@ a b c 1 -1 -1 explain select * from t3 where c >= -2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> select * from t3 where a between 1 and 2; a b c @@ -149,7 +149,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -165,7 +165,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol> select * from t3 where a between 1 and 2 order by b; a b c @@ -173,7 +173,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -181,7 +181,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -205,7 +205,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort +1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -221,7 +221,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> select sum(b) from t1 group by b; sum(b) diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 6922f59bf7e..7e716b8b897 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -44,7 +44,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using where +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -141,7 +141,7 @@ a b c 1 -1 -1 explain select * from t3 where c >= -2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using where +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> select * from t3 where a between 1 and 2; a b c @@ -149,7 +149,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -165,7 +165,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -173,7 +173,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -189,7 +189,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort +1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -205,7 +205,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where; Using filesort +1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -221,7 +221,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using where +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> select sum(b) from t1 group by b; sum(b) diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index e335406493c..a2577e4b71c 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -602,6 +602,7 @@ enabled by default, add OPTIMIZER_SWITCH_MATERIALIZATION OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | \ OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \ + OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN | \ OPTIMIZER_SWITCH_TABLE_ELIMINATION | \ OPTIMIZER_SWITCH_IN_TO_EXISTS | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ |