--- innodb_ext_key.result +++ innodb_ext_key,off.result @@ -9,7 +9,7 @@ explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -19,7 +19,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 1 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -50,7 +50,7 @@ select count(*) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 1 Using where; Using index +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index flush status; select count(*) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; @@ -61,7 +61,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 1 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -71,7 +71,7 @@ select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 3 Using where; Using index +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index flush status; select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -84,7 +84,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 3 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -93,7 +93,7 @@ explain select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE lineitem ref i_l_shipdate i_l_shipdate 4 const 6 Using index flush status; select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; min(l_orderkey) @@ -103,7 +103,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 0 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -113,7 +113,7 @@ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index flush status; select min(l_orderkey) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -124,7 +124,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 0 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -134,7 +134,7 @@ select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index flush status; select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -145,7 +145,7 @@ Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 -Handler_read_next 0 +Handler_read_next 6 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -157,7 +157,7 @@ where l_shipdate='1992-07-01' and l_orderkey=130 or l_receiptdate='1992-07-01' and l_orderkey=5603; 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 2 Using 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 4,4 NULL 9 Using 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) @@ -171,10 +171,10 @@ Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 -Handler_read_next 2 +Handler_read_next 9 Handler_read_prev 0 Handler_read_retry 0 -Handler_read_rnd 2 +Handler_read_rnd 9 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 explain @@ -183,7 +183,7 @@ 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 # 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 9 Using 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) @@ -198,10 +198,10 @@ Handler_read_first 0 Handler_read_key 2 Handler_read_last 0 -Handler_read_next 3 +Handler_read_next 9 Handler_read_prev 0 Handler_read_retry 0 -Handler_read_rnd 3 +Handler_read_rnd 9 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 explain @@ -209,7 +209,7 @@ 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 # NULL # Using +1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY # NULL # Using flush status; select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 @@ -220,12 +220,12 @@ 5959 3 show status like 'handler_read_next'; Variable_name Value -Handler_read_next 3 +Handler_read_next 9 explain select max(l_orderkey) from lineitem where l_partkey between 1 and 10 group by l_partkey; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index for group-by +1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index flush status; select max(l_orderkey) from lineitem where l_partkey between 1 and 10 group by l_partkey; @@ -243,9 +243,9 @@ show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 21 -Handler_read_last 1 -Handler_read_next 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 294 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -255,7 +255,7 @@ select max(l_orderkey) from lineitem where l_suppkey in (1,4) group by l_suppkey; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index for group-by +1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index flush status; select max(l_orderkey) from lineitem where l_suppkey in (1,4) group by l_suppkey; @@ -265,9 +265,9 @@ show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 6 -Handler_read_last 1 -Handler_read_next 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 1230 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -283,7 +283,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE part range i_p_retailprice i_p_retailprice 9 NULL # Using where; Using index 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const # Using index -1 SIMPLE lineitem ref i_l_partkey i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey # Using index +1 SIMPLE lineitem ref i_l_partkey i_l_partkey 5 dbt3_s001.part.p_partkey # Using where; Using index flush status; select o_orderkey, p_partkey from part use index (i_p_retailprice), @@ -297,7 +297,7 @@ Handler_read_first 0 Handler_read_key 3 Handler_read_last 0 -Handler_read_next 3 +Handler_read_next 26 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -314,8 +314,8 @@ select * from t0, part ignore index (primary) where p_partkey=t0.a and p_size=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where -1 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1 +1 SIMPLE t0 ALL NULL NULL NULL NULL 5 +1 SIMPLE part ref i_p_size i_p_size 5 const 5 Using index condition select * from t0, part ignore index (primary) where p_partkey=t0.a and p_size=1; a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment @@ -494,7 +494,7 @@ select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where -1 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index +1 SIMPLE t3 ref PRIMARY,col1 col1 8 test.t1.a,test.t1.a # Using where; Using index drop table t1,t2,t3; # # Bug mdev-4340: performance regression with extended_keys=on @@ -714,13 +714,13 @@ select * from t1 force index(index_date_updated) where index_date_updated= 10 and index_id < 800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range index_date_updated index_date_updated 13 NULL # Using index condition +1 SIMPLE t1 ref index_date_updated index_date_updated 5 const # Using index condition # This used to work from the start: explain select * from t2 force index(index_date_updated) where index_date_updated= 10 and index_id < 800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition +1 SIMPLE t2 ref index_date_updated index_date_updated 5 const # Using index condition drop table t0,t1,t2; # # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' @@ -755,11 +755,12 @@ "select_id": 1, "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["f2"], "key": "f2", - "key_length": "3070", - "used_key_parts": ["f2", "pk1"], + "key_length": "3066", + "used_key_parts": ["f2"], + "ref": ["const"], "rows": 1, "filtered": 100, "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", @@ -788,8 +789,8 @@ "access_type": "range", "possible_keys": ["k1"], "key": "k1", - "key_length": "3011", - "used_key_parts": ["pk1", "f2", "pk2"], + "key_length": "3007", + "used_key_parts": ["pk1", "f2"], "rows": 1, "filtered": 100, "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'",