diff options
| -rw-r--r-- | mysql-test/include/innodb_prefix_index_cluster_optimization.combinations | 5 | ||||
| -rw-r--r-- | mysql-test/include/innodb_prefix_index_cluster_optimization.inc | 1 | ||||
| -rw-r--r-- | mysql-test/r/fast_prefix_index_fetch_innodb.result | 57 | ||||
| -rw-r--r-- | mysql-test/r/innodb_ext_key,off.rdiff | 295 | ||||
| -rw-r--r-- | mysql-test/r/innodb_ext_key.result | 422 | ||||
| -rw-r--r-- | mysql-test/t/fast_prefix_index_fetch_innodb.test | 52 | ||||
| -rw-r--r-- | mysql-test/t/innodb_ext_key.combinations | 5 | ||||
| -rw-r--r-- | mysql-test/t/innodb_ext_key.test | 222 | ||||
| -rw-r--r-- | storage/innobase/row/row0sel.cc | 31 | ||||
| -rw-r--r-- | storage/xtradb/row/row0sel.cc | 32 |
10 files changed, 439 insertions, 683 deletions
diff --git a/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations b/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations new file mode 100644 index 00000000000..ad82f82aa9d --- /dev/null +++ b/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations @@ -0,0 +1,5 @@ +[covering] +innodb_prefix_index_cluster_optimization=on + +[unoptimized] +innodb_prefix_index_cluster_optimization=off diff --git a/mysql-test/include/innodb_prefix_index_cluster_optimization.inc b/mysql-test/include/innodb_prefix_index_cluster_optimization.inc new file mode 100644 index 00000000000..c841fece702 --- /dev/null +++ b/mysql-test/include/innodb_prefix_index_cluster_optimization.inc @@ -0,0 +1 @@ +--source include/have_innodb.inc diff --git a/mysql-test/r/fast_prefix_index_fetch_innodb.result b/mysql-test/r/fast_prefix_index_fetch_innodb.result index c6d96389b08..885ee000c28 100644 --- a/mysql-test/r/fast_prefix_index_fetch_innodb.result +++ b/mysql-test/r/fast_prefix_index_fetch_innodb.result @@ -1,4 +1,4 @@ -drop table if exists prefixinno; +SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization; set global innodb_prefix_index_cluster_optimization = ON; show variables like 'innodb_prefix_index_cluster_optimization'; Variable_name Value @@ -346,10 +346,10 @@ f1 🐱🌑 select @cluster_lookups; @cluster_lookups -2 +1 select @cluster_lookups_avoided; @cluster_lookups_avoided -0 +1 # Eligible - record length is shorter than prefix length SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%'; f1 @@ -366,10 +366,10 @@ f1 🌒 select @cluster_lookups; @cluster_lookups -1 +0 select @cluster_lookups_avoided; @cluster_lookups_avoided -1 +2 DROP TABLE t1; CREATE TABLE t1( col1 INT, @@ -398,4 +398,49 @@ select @cluster_lookups_avoided; @cluster_lookups_avoided 0 DROP TABLE t1; -set global innodb_prefix_index_cluster_optimization = OFF; +# +# MDEV-23600 Division by 0 in row_search_with_covering_prefix() +# +CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB; +INSERT t SET c=POINT(1,1); +SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t); +c +DROP TABLE t; +# +# MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization +# +CREATE TABLE wp_blogs ( +blog_id bigint(20) NOT NULL auto_increment, +site_id bigint(20) NOT NULL default '0', +domain varchar(200) NOT NULL default '', +path varchar(100) NOT NULL default '', +registered datetime NOT NULL default '0000-00-00 00:00:00', +last_updated datetime NOT NULL default '0000-00-00 00:00:00', +public tinyint(2) NOT NULL default '1', +archived tinyint(2) NOT NULL default '0', +mature tinyint(2) NOT NULL default '0', +spam tinyint(2) NOT NULL default '0', +deleted tinyint(2) NOT NULL default '0', +lang_id int(11) NOT NULL default '0', +PRIMARY KEY (blog_id), +KEY domain (domain(50),path(5)), +KEY lang_id (lang_id) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +INSERT INTO wp_blogs (domain, path) VALUES +('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'), +('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'), +('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar'); +SET GLOBAL innodb_prefix_index_cluster_optimization=off; +SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') +AND path IN ( '/fondsinvesteringer/', '/' ); +blog_id +2 +1 +SET GLOBAL innodb_prefix_index_cluster_optimization=on; +SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') +AND path IN ( '/fondsinvesteringer/', '/' ); +blog_id +2 +1 +DROP TABLE wp_blogs; +SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt; diff --git a/mysql-test/r/innodb_ext_key,off.rdiff b/mysql-test/r/innodb_ext_key,off.rdiff new file mode 100644 index 00000000000..47ca1e6849a --- /dev/null +++ b/mysql-test/r/innodb_ext_key,off.rdiff @@ -0,0 +1,295 @@ +@@ -4,7 +4,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 PRIMARY 4 const 5 Using where + flush status; + select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; + count(*) +@@ -14,7 +14,7 @@ + Handler_read_first 0 + Handler_read_key 1 + Handler_read_last 0 +-Handler_read_next 1 ++Handler_read_next 5 + Handler_read_prev 0 + Handler_read_retry 0 + Handler_read_rnd 0 +@@ -45,7 +45,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; +@@ -56,7 +56,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 +@@ -66,7 +66,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; +@@ -79,7 +79,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 +@@ -88,7 +88,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) +@@ -98,7 +98,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 +@@ -108,7 +108,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; +@@ -119,7 +119,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 +@@ -129,7 +129,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 PRIMARY 4 const 5 Using where + flush status; + select max(l_linenumber) from lineitem + where l_shipdate='1992-07-01' and l_orderkey=130; +@@ -140,7 +140,7 @@ + Handler_read_first 0 + Handler_read_key 1 + Handler_read_last 0 +-Handler_read_next 0 ++Handler_read_next 5 + Handler_read_prev 0 + Handler_read_retry 0 + Handler_read_rnd 0 +@@ -152,7 +152,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) +@@ -166,10 +166,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 +@@ -178,7 +178,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 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 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) +@@ -193,10 +193,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 +@@ -204,7 +204,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 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,PRIMARY,i_l_receiptdate,PRIMARY 4,4,4,4 NULL 2 Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where + flush status; + select l_orderkey, l_linenumber from lineitem + where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 +@@ -218,7 +218,7 @@ + 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 +@@ -228,7 +228,7 @@ + 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; +@@ -246,9 +246,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 +@@ -258,7 +258,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; +@@ -268,9 +268,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 +@@ -286,7 +286,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), +@@ -300,7 +300,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 +@@ -317,8 +317,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 +@@ -495,7 +495,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 +@@ -705,13 +705,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' +@@ -746,11 +746,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'", +@@ -779,8 +780,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'", diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index e02b78cad15..233b359157b 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -1,30 +1,6 @@ -DROP TABLE IF EXISTS t1,t2,t3,t4; -DROP DATABASE IF EXISTS dbt3_s001; SET SESSION STORAGE_ENGINE='InnoDB'; CREATE DATABASE dbt3_s001; use dbt3_s001; -set @save_ext_key_optimizer_switch=@@optimizer_switch; -set optimizer_switch='extended_keys=off'; -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 PRIMARY 4 const 5 Using where -flush status; -select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; -count(*) -1 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 5 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; 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 @@ -44,29 +20,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -select count(*) from lineitem -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 -flush status; -select count(*) from lineitem -where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; -count(*) -1 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 0 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; @@ -88,29 +41,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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 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; -count(*) -1 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 6 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; @@ -132,31 +62,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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 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; -l_orderkey l_linenumber -1088 3 -1217 1 -1221 3 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 6 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -180,27 +85,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -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 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) -130 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 6 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; 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 @@ -220,29 +104,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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 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; -min(l_orderkey) -1088 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 6 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select min(l_orderkey) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -264,29 +125,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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 lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where -flush status; -select max(l_linenumber) from lineitem -where l_shipdate='1992-07-01' and l_orderkey=130; -max(l_linenumber) -2 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 1 -Handler_read_last 0 -Handler_read_next 5 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -308,34 +146,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -select l_orderkey, l_linenumber -from lineitem use index (i_l_shipdate, i_l_receiptdate) -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 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) -where l_shipdate='1992-07-01' and l_orderkey=130 -or l_receiptdate='1992-07-01' and l_orderkey=5603; -l_orderkey l_linenumber -130 2 -5603 2 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 2 -Handler_read_last 0 -Handler_read_next 9 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 9 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select l_orderkey, l_linenumber from lineitem use index (i_l_shipdate, i_l_receiptdate) @@ -362,35 +172,6 @@ Handler_read_retry 0 Handler_read_rnd 2 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -select l_orderkey, l_linenumber -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 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) -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; -l_orderkey l_linenumber -130 2 -5603 2 -5959 3 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 2 -Handler_read_last 0 -Handler_read_next 9 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 9 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select l_orderkey, l_linenumber from lineitem use index (i_l_shipdate, i_l_receiptdate) @@ -418,33 +199,6 @@ Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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,PRIMARY,i_l_receiptdate,PRIMARY 4,4,4,4 NULL 2 Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where -flush status; -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; -l_orderkey l_linenumber -130 2 -5603 2 -5959 3 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 2 -Handler_read_last 0 -Handler_read_next 9 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 3 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 @@ -470,38 +224,6 @@ Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -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 -flush status; -select max(l_orderkey) from lineitem -where l_partkey between 1 and 10 group by l_partkey; -max(l_orderkey) -5984 -5957 -5892 -5856 -5959 -5957 -5794 -5894 -5859 -5632 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 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 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select max(l_orderkey) from lineitem where l_partkey between 1 and 10 group by l_partkey; @@ -532,30 +254,6 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=off'; -explain -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 -flush status; -select max(l_orderkey) from lineitem -where l_suppkey in (1,4) group by l_suppkey; -max(l_orderkey) -5988 -5984 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 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 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select max(l_orderkey) from lineitem where l_suppkey in (1,4) group by l_suppkey; @@ -579,37 +277,6 @@ Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 create index i_p_retailprice on part(p_retailprice); -set optimizer_switch='extended_keys=off'; -explain -select o_orderkey, p_partkey -from part use index (i_p_retailprice), -lineitem use index (i_l_partkey), orders -where p_retailprice > 1100 and o_orderdate='1997-01-01' -and o_orderkey=l_orderkey and p_partkey=l_partkey; -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 5 dbt3_s001.part.p_partkey # Using where; Using index -flush status; -select o_orderkey, p_partkey -from part use index (i_p_retailprice), -lineitem use index (i_l_partkey), orders -where p_retailprice > 1100 and o_orderdate='1997-01-01' -and o_orderkey=l_orderkey and p_partkey=l_partkey; -o_orderkey p_partkey -5895 200 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 3 -Handler_read_last 0 -Handler_read_next 26 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 0 -set optimizer_switch='extended_keys=on'; explain select o_orderkey, p_partkey from part use index (i_p_retailprice), @@ -646,7 +313,6 @@ Handler_read_rnd_next 0 create table t0 (a int); insert into t0 values (1), (2), (3), (4), (5); create index i_p_size on part(p_size); -set optimizer_switch='extended_keys=on'; explain select * from t0, part ignore index (primary) where p_partkey=t0.a and p_size=1; @@ -667,7 +333,6 @@ use test; # set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='materialization=on,semijoin=on'; -SET optimizer_switch='extended_keys=on'; CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1), (2,2); SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); @@ -688,9 +353,7 @@ set optimizer_switch=@save_optimizer_switch; # + extended_keys = on # (valgrinf complains fixed by the patch for bug #914560) # -set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch = 'derived_with_keys=on'; -SET optimizer_switch = 'extended_keys=on'; CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('j'), ('v'); CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM; @@ -720,21 +383,6 @@ c int NOT NULL PRIMARY KEY INSERT INTO t2 VALUES (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (24); -set @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch = 'extended_keys=off'; -EXPLAIN -SELECT a FROM t1 AS t, t2 -WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index -SELECT a FROM t1 AS t, t2 -WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); -a -24 -SET optimizer_switch = 'extended_keys=on'; EXPLAIN SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); @@ -747,7 +395,6 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); a 24 -set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; # # LP Bug #923236: hash join + extended_keys = on @@ -756,12 +403,10 @@ CREATE TABLE t1 (a int) ENGINE=MyISAM; CREATE TABLE t2 (b int) ENGINE=MyISAM; INSERT INTO t1 (a) VALUES (4), (6); INSERT INTO t2 (b) VALUES (0), (8); -set @save_optimizer_switch=@@optimizer_switch; set @save_join_cache_level=@@join_cache_level; SET join_cache_level=3; SET optimizer_switch='join_cache_hashed=on'; SET optimizer_switch='join_cache_bka=on'; -SET optimizer_switch='extended_keys=on'; EXPLAIN SELECT * FROM t1, t2 WHERE b=a; id select_type table type possible_keys key key_len ref rows Extra @@ -786,26 +431,16 @@ UNIQUE KEY uq (c2,c3), KEY c3 (c3), KEY c4 (c4) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -set @save_optimizer_switch=@@optimizer_switch; -set session optimizer_switch='extended_keys=off'; -INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') -ON DUPLICATE KEY UPDATE c4 = VALUES(c4); INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') ON DUPLICATE KEY UPDATE c4 = VALUES(c4); -DELETE FROM t1; -set session optimizer_switch='extended_keys=on'; INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') ON DUPLICATE KEY UPDATE c4 = VALUES(c4); -INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') -ON DUPLICATE KEY UPDATE c4 = VALUES(c4); -set optimizer_switch=@save_optimizer_switch; DROP TABLE t1; # # Bug mdev-4220: using ref instead of eq_ref # with extended_keys=on # (performance regression introduced in the patch for mdev-3851) # -set @save_optimizer_switch=@@optimizer_switch; create table t1 (a int not null) engine=innodb; insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 ( @@ -815,29 +450,6 @@ insert into t2 select A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a from t1 A, t1 B; -set optimizer_switch='extended_keys=off'; -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 -flush status; -select * from t1, t2 where t2.a=t1.a and t2.b < 2; -a pk a b -0 0 0 0 -1 1 1 1 -show status like 'handler_read%'; -Variable_name Value -Handler_read_first 0 -Handler_read_key 10 -Handler_read_last 0 -Handler_read_next 0 -Handler_read_prev 0 -Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 11 -set optimizer_switch='extended_keys=on'; 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 @@ -874,18 +486,6 @@ analyze table t1,t3; Table Op Msg_type Msg_text test.t1 analyze status OK test.t3 analyze status OK -set optimizer_switch='extended_keys=off'; -explain -select * from t1, t3 where t3.col1=t1.a and t3.col2=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 col1 col1 8 test.t1.a,test.t1.a # Using index -explain -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 8 test.t1.a,test.t1.a # Using where; Using index -set optimizer_switch='extended_keys=on'; explain select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; id select_type table type possible_keys key key_len ref rows Extra @@ -900,7 +500,6 @@ drop table t1,t2,t3; # # Bug mdev-4340: performance regression with extended_keys=on # -set @save_optimizer_switch=@@optimizer_switch; CREATE TABLE t1 ( page_id int(8) unsigned NOT NULL AUTO_INCREMENT, page_namespace int(11) NOT NULL DEFAULT '0', @@ -1000,16 +599,6 @@ INSERT INTO t3 VALUES (89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''), (93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''), (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009',''); -set optimizer_switch='extended_keys=off'; -EXPLAIN -SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 -WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' -ORDER BY rev_timestamp ASC LIMIT 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY,name_title name_title 261 const,const 1 -1 SIMPLE t2 ref page_timestamp page_timestamp 4 const 10 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.rev_text_id 1 -set optimizer_switch='extended_keys=on'; EXPLAIN SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' @@ -1027,7 +616,6 @@ 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); -set optimizer_switch='extended_keys=on'; 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 @@ -1042,14 +630,6 @@ select a from t2 where b is null order by a desc limit 2; a 3 2 -set optimizer_switch='extended_keys=off'; -explain select a from t2 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 t2 range b b 9 NULL 3 Using where; Using filesort -select a from t2 where b is null order by a desc limit 2; -a -3 -2 explain select a from t2 where b is null order by a desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where @@ -1067,7 +647,6 @@ a 2 1 drop table t1, t2; -set optimizer_switch=@save_optimizer_switch; # # MDEV-10325: Queries examines all rows of a tables when it should not # @@ -1213,7 +792,6 @@ EXPLAIN } } drop table t1; -set optimizer_switch=@save_ext_key_optimizer_switch; set global innodb_file_format = @save_innodb_file_format; set global innodb_large_prefix = @save_innodb_large_prefix; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/fast_prefix_index_fetch_innodb.test b/mysql-test/t/fast_prefix_index_fetch_innodb.test index c3b3440d82d..7112f14bbd5 100644 --- a/mysql-test/t/fast_prefix_index_fetch_innodb.test +++ b/mysql-test/t/fast_prefix_index_fetch_innodb.test @@ -1,9 +1,6 @@ -- source include/have_innodb.inc ---disable_warnings -drop table if exists prefixinno; ---enable_warnings - +SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization; set global innodb_prefix_index_cluster_optimization = ON; show variables like 'innodb_prefix_index_cluster_optimization'; @@ -665,4 +662,49 @@ select @cluster_lookups; select @cluster_lookups_avoided; DROP TABLE t1; -set global innodb_prefix_index_cluster_optimization = OFF; + +--echo # +--echo # MDEV-23600 Division by 0 in row_search_with_covering_prefix() +--echo # +CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB; +INSERT t SET c=POINT(1,1); +SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t); +DROP TABLE t; + +--echo # +--echo # MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization +--echo # +CREATE TABLE wp_blogs ( + blog_id bigint(20) NOT NULL auto_increment, + site_id bigint(20) NOT NULL default '0', + domain varchar(200) NOT NULL default '', + path varchar(100) NOT NULL default '', + registered datetime NOT NULL default '0000-00-00 00:00:00', + last_updated datetime NOT NULL default '0000-00-00 00:00:00', + public tinyint(2) NOT NULL default '1', + archived tinyint(2) NOT NULL default '0', + mature tinyint(2) NOT NULL default '0', + spam tinyint(2) NOT NULL default '0', + deleted tinyint(2) NOT NULL default '0', + lang_id int(11) NOT NULL default '0', + PRIMARY KEY (blog_id), + KEY domain (domain(50),path(5)), + KEY lang_id (lang_id) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; + +INSERT INTO wp_blogs (domain, path) VALUES +('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'), +('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'), +('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar'); + +SET GLOBAL innodb_prefix_index_cluster_optimization=off; +SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') +AND path IN ( '/fondsinvesteringer/', '/' ); + +SET GLOBAL innodb_prefix_index_cluster_optimization=on; +SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') +AND path IN ( '/fondsinvesteringer/', '/' ); + +DROP TABLE wp_blogs; + +SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt; diff --git a/mysql-test/t/innodb_ext_key.combinations b/mysql-test/t/innodb_ext_key.combinations new file mode 100644 index 00000000000..6ad97eaf75b --- /dev/null +++ b/mysql-test/t/innodb_ext_key.combinations @@ -0,0 +1,5 @@ +[on] +optimizer_switch=extended_keys=on + +[off] +optimizer_switch=extended_keys=off diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index bec4462df08..c936e4b5170 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -1,9 +1,4 @@ ---source include/have_innodb.inc - ---disable_warnings -DROP TABLE IF EXISTS t1,t2,t3,t4; -DROP DATABASE IF EXISTS dbt3_s001; ---enable_warnings +--source include/innodb_prefix_index_cluster_optimization.inc SET SESSION STORAGE_ENGINE='InnoDB'; @@ -19,32 +14,12 @@ use dbt3_s001; --enable_result_log --enable_query_log -set @save_ext_key_optimizer_switch=@@optimizer_switch; - -set optimizer_switch='extended_keys=off'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; -flush status; -select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; -explain -select count(*) from lineitem - where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; -flush status; -select count(*) from lineitem - where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; @@ -53,16 +28,6 @@ select count(*) from lineitem where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; show status like 'handler_read%'; -set optimizer_switch='extended_keys=off'; -explain -select count(*) from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; -flush status; -select count(*) from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; @@ -71,16 +36,6 @@ select count(*) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; show status like 'handler_read%'; -set optimizer_switch='extended_keys=off'; -explain -select l_orderkey, l_linenumber from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; -flush status; -select l_orderkey, l_linenumber from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; explain select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -89,21 +44,12 @@ select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; show status like 'handler_read%'; -set optimizer_switch='extended_keys=off'; explain select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; flush status; select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; -flush status; -select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; explain select min(l_orderkey) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; @@ -112,25 +58,6 @@ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -select min(l_orderkey) from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; -flush status; -select min(l_orderkey) from lineitem - where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; -explain -select max(l_linenumber) from lineitem - where l_shipdate='1992-07-01' and l_orderkey=130; -flush status; -select max(l_linenumber) from lineitem - where l_shipdate='1992-07-01' and l_orderkey=130; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; explain select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -139,7 +66,6 @@ select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; show status like 'handler_read%'; -set optimizer_switch='extended_keys=off'; explain select l_orderkey, l_linenumber from lineitem use index (i_l_shipdate, i_l_receiptdate) @@ -152,20 +78,6 @@ select l_orderkey, l_linenumber or l_receiptdate='1992-07-01' and l_orderkey=5603; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -select l_orderkey, l_linenumber - from lineitem use index (i_l_shipdate, i_l_receiptdate) - where l_shipdate='1992-07-01' and l_orderkey=130 - or l_receiptdate='1992-07-01' and l_orderkey=5603; -flush status; -select l_orderkey, l_linenumber - from lineitem use index (i_l_shipdate, i_l_receiptdate) - where l_shipdate='1992-07-01' and l_orderkey=130 - or l_receiptdate='1992-07-01' and l_orderkey=5603; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; explain select l_orderkey, l_linenumber from lineitem use index (i_l_shipdate, i_l_receiptdate) @@ -178,20 +90,6 @@ select l_orderkey, l_linenumber or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -select l_orderkey, l_linenumber - 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; -flush status; -select l_orderkey, l_linenumber - 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; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; explain select l_orderkey, l_linenumber from lineitem where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 @@ -202,28 +100,6 @@ select l_orderkey, l_linenumber from lineitem or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; show status like 'handler_read%'; -set optimizer_switch='extended_keys=on'; -explain -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; -flush status; -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; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=off'; ---replace_column 9 # -explain -select max(l_orderkey) from lineitem - where l_partkey between 1 and 10 group by l_partkey; -flush status; -select max(l_orderkey) from lineitem - where l_partkey between 1 and 10 group by l_partkey; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; --replace_column 9 # explain select max(l_orderkey) from lineitem @@ -233,17 +109,6 @@ select max(l_orderkey) from lineitem where l_partkey between 1 and 10 group by l_partkey; show status like 'handler_read%'; -set optimizer_switch='extended_keys=off'; ---replace_column 9 # -explain -select max(l_orderkey) from lineitem - where l_suppkey in (1,4) group by l_suppkey; -flush status; -select max(l_orderkey) from lineitem - where l_suppkey in (1,4) group by l_suppkey; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; --replace_column 9 # explain select max(l_orderkey) from lineitem @@ -255,23 +120,6 @@ show status like 'handler_read%'; create index i_p_retailprice on part(p_retailprice); -set optimizer_switch='extended_keys=off'; ---replace_column 9 # -explain -select o_orderkey, p_partkey - from part use index (i_p_retailprice), - lineitem use index (i_l_partkey), orders - where p_retailprice > 1100 and o_orderdate='1997-01-01' - and o_orderkey=l_orderkey and p_partkey=l_partkey; -flush status; -select o_orderkey, p_partkey - from part use index (i_p_retailprice), - lineitem use index (i_l_partkey), orders - where p_retailprice > 1100 and o_orderdate='1997-01-01' - and o_orderkey=l_orderkey and p_partkey=l_partkey; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; --replace_column 9 # explain select o_orderkey, p_partkey @@ -296,8 +144,6 @@ create table t0 (a int); insert into t0 values (1), (2), (3), (4), (5); create index i_p_size on part(p_size); -set optimizer_switch='extended_keys=on'; - explain select * from t0, part ignore index (primary) where p_partkey=t0.a and p_size=1; @@ -320,7 +166,6 @@ use test; set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='materialization=on,semijoin=on'; -SET optimizer_switch='extended_keys=on'; CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1), (2,2); @@ -339,10 +184,7 @@ set optimizer_switch=@save_optimizer_switch; --echo # (valgrinf complains fixed by the patch for bug #914560) --echo # -set @save_optimizer_switch=@@optimizer_switch; - SET optimizer_switch = 'derived_with_keys=on'; -SET optimizer_switch = 'extended_keys=on'; CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('j'), ('v'); @@ -382,24 +224,12 @@ INSERT INTO t2 VALUES (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (24); -set @save_optimizer_switch=@@optimizer_switch; - -SET optimizer_switch = 'extended_keys=off'; -EXPLAIN -SELECT a FROM t1 AS t, t2 - WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); -SELECT a FROM t1 AS t, t2 - WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); - -SET optimizer_switch = 'extended_keys=on'; EXPLAIN SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); -set optimizer_switch=@save_optimizer_switch; - DROP TABLE t1,t2; --echo # @@ -413,13 +243,11 @@ CREATE TABLE t2 (b int) ENGINE=MyISAM; INSERT INTO t1 (a) VALUES (4), (6); INSERT INTO t2 (b) VALUES (0), (8); -set @save_optimizer_switch=@@optimizer_switch; set @save_join_cache_level=@@join_cache_level; SET join_cache_level=3; SET optimizer_switch='join_cache_hashed=on'; SET optimizer_switch='join_cache_bka=on'; -SET optimizer_switch='extended_keys=on'; EXPLAIN SELECT * FROM t1, t2 WHERE b=a; @@ -448,24 +276,11 @@ KEY c4 (c4) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -set @save_optimizer_switch=@@optimizer_switch; - -set session optimizer_switch='extended_keys=off'; -INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') - ON DUPLICATE KEY UPDATE c4 = VALUES(c4); -INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') - ON DUPLICATE KEY UPDATE c4 = VALUES(c4); - -DELETE FROM t1; - -set session optimizer_switch='extended_keys=on'; INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') ON DUPLICATE KEY UPDATE c4 = VALUES(c4); INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') ON DUPLICATE KEY UPDATE c4 = VALUES(c4); -set optimizer_switch=@save_optimizer_switch; - DROP TABLE t1; --echo # @@ -474,8 +289,6 @@ DROP TABLE t1; --echo # (performance regression introduced in the patch for mdev-3851) --echo # -set @save_optimizer_switch=@@optimizer_switch; - create table t1 (a int not null) engine=innodb; insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -489,14 +302,6 @@ select A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a from t1 A, t1 B; -set optimizer_switch='extended_keys=off'; -explain -select * from t1, t2 where t2.a=t1.a and t2.b < 2; -flush status; -select * from t1, t2 where t2.a=t1.a and t2.b < 2; -show status like 'handler_read%'; - -set optimizer_switch='extended_keys=on'; explain select * from t1, t2 where t2.a=t1.a and t2.b < 2; flush status; @@ -522,15 +327,6 @@ alter table t3 add primary key (pk1, pk2); alter table t3 add key (col1, col2); analyze table t1,t3; -set optimizer_switch='extended_keys=off'; ---replace_column 9 # -explain -select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; ---replace_column 9 # -explain -select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; - -set optimizer_switch='extended_keys=on'; --replace_column 9 # explain select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; @@ -544,8 +340,6 @@ drop table t1,t2,t3; --echo # Bug mdev-4340: performance regression with extended_keys=on --echo # -set @save_optimizer_switch=@@optimizer_switch; - CREATE TABLE t1 ( page_id int(8) unsigned NOT NULL AUTO_INCREMENT, page_namespace int(11) NOT NULL DEFAULT '0', @@ -649,13 +443,6 @@ INSERT INTO t3 VALUES (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009',''); -set optimizer_switch='extended_keys=off'; -EXPLAIN -SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 - WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' -ORDER BY rev_timestamp ASC LIMIT 10; - -set optimizer_switch='extended_keys=on'; EXPLAIN SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' @@ -674,14 +461,10 @@ create table t2 (a bigint not null unique auto_increment, b varchar(10), primary insert into t1 (b) values (null), (null), (null); insert into t2 (b) values (null), (null), (null); -set optimizer_switch='extended_keys=on'; explain select a from t1 where b is null order by a desc limit 2; select a from t1 where b is null order by a desc limit 2; explain select a from t2 where b is null order by a desc limit 2; select a from t2 where b is null order by a desc limit 2; -set optimizer_switch='extended_keys=off'; -explain select a from t2 where b is null order by a desc limit 2; -select a from t2 where b is null order by a desc limit 2; explain select a from t2 where b is null order by a desc; select a from t2 where b is null order by a desc; @@ -691,8 +474,6 @@ select a from t2 where b is null order by a desc,a,a; drop table t1, t2; -set optimizer_switch=@save_optimizer_switch; - --echo # --echo # MDEV-10325: Queries examines all rows of a tables when it should not --echo # @@ -828,7 +609,6 @@ explain format= json select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; drop table t1; -set optimizer_switch=@save_ext_key_optimizer_switch; set global innodb_file_format = @save_innodb_file_format; set global innodb_large_prefix = @save_innodb_large_prefix; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 936b0eccfb7..f45702157c8 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -2,7 +2,7 @@ Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, Google Inc. -Copyright (c) 2015, 2018, MariaDB Corporation. +Copyright (c) 2015, 2020, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -3723,9 +3723,16 @@ bool row_search_with_covering_prefix( return false; } + /* We can avoid a clustered index lookup if + all of the following hold: + (1) all columns are in the secondary index + (2) all values for columns that are prefix-only + indexes are shorter than the prefix size + This optimization can avoid many IOs for certain schemas. */ for (ulint i = 0; i < prebuilt->n_template; i++) { mysql_row_templ_t* templ = prebuilt->mysql_template + i; ulint j = templ->rec_prefix_field_no; + ut_ad(!templ->mbminlen == !templ->mbmaxlen); /** Condition (1) : is the field in the index. */ if (j == ULINT_UNDEFINED) { @@ -3735,33 +3742,29 @@ bool row_search_with_covering_prefix( /** Condition (2): If this is a prefix index then row's value size shorter than prefix length. */ - if (!templ->rec_field_is_prefix) { + if (!templ->rec_field_is_prefix + || rec_offs_nth_sql_null(offsets, j)) { continue; } - ulint rec_size = rec_offs_nth_size(offsets, j); const dict_field_t* field = dict_index_get_nth_field(index, j); - ulint max_chars = field->prefix_len / templ->mbmaxlen; - ut_a(field->prefix_len > 0); - - if (rec_size < max_chars) { - /* Record in bytes shorter than the index - prefix length in char. */ + if (!field->prefix_len) { continue; } - if (rec_size * templ->mbminlen >= field->prefix_len) { + const ulint rec_size = rec_offs_nth_size(offsets, j); + + if (rec_size >= field->prefix_len) { /* Shortest representation string by the byte length of the record is longer than the maximum possible index prefix. */ return false; } - size_t num_chars = rec_field_len_in_chars( - field->col, j, rec, offsets); - - if (num_chars >= max_chars) { + if (templ->mbminlen != templ->mbmaxlen + && rec_field_len_in_chars(field->col, j, rec, offsets) + >= field->prefix_len / templ->mbmaxlen) { /* No of chars to store the record exceeds the index prefix character length. */ return false; diff --git a/storage/xtradb/row/row0sel.cc b/storage/xtradb/row/row0sel.cc index c9c7f1dd9c1..a968fc83243 100644 --- a/storage/xtradb/row/row0sel.cc +++ b/storage/xtradb/row/row0sel.cc @@ -2,7 +2,7 @@ Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, Google Inc. -Copyright (c) 2015, 2018, MariaDB Corporation. +Copyright (c) 2015, 2020, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -3740,9 +3740,16 @@ bool row_search_with_covering_prefix( return false; } + /* We can avoid a clustered index lookup if + all of the following hold: + (1) all columns are in the secondary index + (2) all values for columns that are prefix-only + indexes are shorter than the prefix size + This optimization can avoid many IOs for certain schemas. */ for (ulint i = 0; i < prebuilt->n_template; i++) { mysql_row_templ_t* templ = prebuilt->mysql_template + i; ulint j = templ->rec_prefix_field_no; + ut_ad(!templ->mbminlen == !templ->mbmaxlen); /** Condition (1) : is the field in the index. */ if (j == ULINT_UNDEFINED) { @@ -3752,34 +3759,29 @@ bool row_search_with_covering_prefix( /** Condition (2): If this is a prefix index then row's value size shorter than prefix length. */ - if (!templ->rec_field_is_prefix) { + if (!templ->rec_field_is_prefix + || rec_offs_nth_sql_null(offsets, j)) { continue; } - ulint rec_size = rec_offs_nth_size(offsets, j); const dict_field_t* field = dict_index_get_nth_field(index, j); - ulint max_chars = field->prefix_len / templ->mbmaxlen; - ut_a(field->prefix_len > 0); - - if (rec_size < max_chars) { - /* Record in bytes shorter than the index - prefix length in char. */ + if (!field->prefix_len) { continue; } - if (rec_size * templ->mbminlen >= field->prefix_len) { + const ulint rec_size = rec_offs_nth_size(offsets, j); + + if (rec_size >= field->prefix_len) { /* Shortest representation string by the byte length of the record is longer than the maximum possible index prefix. */ return false; } - - size_t num_chars = rec_field_len_in_chars( - field->col, j, rec, offsets); - - if (num_chars >= max_chars) { + if (templ->mbminlen != templ->mbmaxlen + && rec_field_len_in_chars(field->col, j, rec, offsets) + >= field->prefix_len / templ->mbmaxlen) { /* No of chars to store the record exceeds the index prefix character length. */ return false; |