diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/key.result | 52 |
1 files changed, 47 insertions, 5 deletions
diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index 2e2c8d89..e6bb46af 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -231,11 +231,14 @@ numeropost 1 EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const numreponse numreponse 4 const 1 Using index +1 SIMPLE t1 const numreponse numreponse 4 const 1 FLUSH TABLES; SELECT numeropost FROM t1 WHERE numreponse='1'; numeropost 1 +EXPLAIN SELECT numreponse+0 FROM t1 WHERE numreponse='1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const numreponse numreponse 4 const 1 Using index drop table t1; create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; show create table t1; @@ -610,7 +613,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 range a a 5 NULL 5 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES @@ -628,19 +631,19 @@ 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 SHOW STATUS LIKE 'Last_query_cost'; Variable_name Value -Last_query_cost 9.212184 +Last_query_cost 0.014784 EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; 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 SHOW STATUS LIKE 'Last_query_cost'; Variable_name Value -Last_query_cost 9.212184 +Last_query_cost 0.014784 EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 6 SHOW STATUS LIKE 'Last_query_cost'; Variable_name Value -Last_query_cost 14.199000 +Last_query_cost 0.014784 DROP TABLE t1; # # MDEV-21480: Unique key using ref access though eq_ref access can be used @@ -689,3 +692,42 @@ drop table t1,t2; # create table t1 (a int, b int, key(a), key(a desc)); drop table t1; +# Check some issues with FORCE INDEX and full index scans +# (Does FORCE INDEX force an index scan) +# +create table t1 (a int primary key, b int, c int, d int, +key k1 (b) using BTREE, key k2 (c,d) using btree) engine=heap; +insert into t1 select seq as a, seq as b, seq as c, seq as d +from seq_1_to_100; +explain select sum(a+b) from t1 force index (k1) where b>0 and a=99; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range k1 k1 5 NULL 100 Using where +explain select sum(a+b) from t1 force index (k1) where a>0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +explain select sum(a+b) from t1 force index (k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +explain select sum(a+b) from t1 force index for join (k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +explain select sum(a+b) from t1 force index for order by (k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +explain select sum(a+b) from t1 force index (k1,k2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +select sum(a+b) from t1 force index (k1); +sum(a+b) +10100 +explain select sum(a+b) from t1 force index (primary); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +select sum(a+b) from t1 force index (primary); +sum(a+b) +10100 +explain select straight_join sum(a+b) from seq_1_to_10 as s, t1 force index (k2) where t1.a=s.seq; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE s index PRIMARY PRIMARY 8 NULL 10 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +drop table t1; |