diff options
Diffstat (limited to 'mysql-test/main/innodb_ext_key.result')
| -rw-r--r-- | mysql-test/main/innodb_ext_key.result | 47 |
1 files changed, 27 insertions, 20 deletions
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index 9ce41999b6f..6fba116d35d 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -1,4 +1,9 @@ SET SESSION STORAGE_ENGINE='InnoDB'; +set @innodb_stats_persistent_save= @@innodb_stats_persistent; +set @innodb_stats_persistent_sample_pages_save= +@@innodb_stats_persistent_sample_pages; +set global innodb_stats_persistent= 1; +set global innodb_stats_persistent_sample_pages=100; CREATE DATABASE dbt3_s001; use dbt3_s001; explain @@ -21,12 +26,12 @@ Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 explain -select count(*) from lineitem +select count(*) from lineitem use index(primary) where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem const PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 const,const 1 +1 SIMPLE lineitem const PRIMARY PRIMARY 8 const,const 1 flush status; -select count(*) from lineitem +select count(*) from lineitem use index(primary) where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; count(*) 1 @@ -178,7 +183,7 @@ from lineitem use index (i_l_shipdate, i_l_receiptdate) where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where +1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate # NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where flush status; select l_orderkey, l_linenumber from lineitem use index (i_l_shipdate, i_l_receiptdate) @@ -204,7 +209,7 @@ select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where +1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate # NULL # Using flush status; select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 @@ -213,17 +218,9 @@ l_orderkey l_linenumber 130 2 5603 2 5959 3 -show status like 'handler_read%'; +show status like 'handler_read_next'; Variable_name Value -Handler_read_first 0 -Handler_read_key 2 -Handler_read_last 0 Handler_read_next 3 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 3 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 explain select max(l_orderkey) from lineitem where l_partkey between 1 and 10 group by l_partkey; @@ -453,8 +450,8 @@ from t1 A, t1 B; explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL # +1 SIMPLE t2 eq_ref a a 4 test.t1.a # Using where flush status; select * from t1, t2 where t2.a=t1.a and t2.b < 2; a pk a b @@ -484,7 +481,9 @@ alter table t3 add primary key (pk1, pk2); alter table t3 add key (col1, col2); analyze table t1,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK explain select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; @@ -616,9 +615,15 @@ create table t1 (a bigint not null unique auto_increment, b varchar(10), primary create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8; insert into t1 (b) values (null), (null), (null); insert into t2 (b) values (null), (null), (null); +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK explain select a from t1 where b is null order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index b PRIMARY 8 NULL 3 Using where +1 SIMPLE t1 index b PRIMARY 8 NULL 2 Using where select a from t1 where b is null order by a desc limit 2; a 3 @@ -716,7 +721,7 @@ drop table t0,t1,t2; # # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' # was corrupted, server crashes in opt_sum_query -SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@@optimizer_use_condition_selectivity=1; +SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity, @@optimizer_use_condition_selectivity=4; CREATE TABLE t1 ( pk INT, f1 VARCHAR(3), @@ -752,7 +757,7 @@ EXPLAIN "key_length": "3070", "used_key_parts": ["f2", "pk1"], "rows": 1, - "filtered": 100, + "filtered": 50, "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", "attached_condition": "t1.f1 <= '3'" } @@ -782,7 +787,7 @@ EXPLAIN "key_length": "3011", "used_key_parts": ["pk1", "f2", "pk2"], "rows": 1, - "filtered": 100, + "filtered": 50, "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", "attached_condition": "t1.f1 <= '3'" } @@ -806,3 +811,5 @@ EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k1 23 NULL # Using index DROP TABLE t1,t2; +set global innodb_stats_persistent= @innodb_stats_persistent_save; +set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; |