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 | 68 | ||||
| -rw-r--r-- | mysql-test/r/innodb_ext_key,off.rdiff | 295 | ||||
| -rw-r--r-- | mysql-test/r/innodb_ext_key.result | 421 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/temp_table_symlink.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/fast_prefix_index_fetch_innodb.test | 61 | ||||
| -rw-r--r-- | mysql-test/t/innodb_ext_key.combinations | 5 | ||||
| -rw-r--r-- | mysql-test/t/innodb_ext_key.test | 221 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 15 | ||||
| -rw-r--r-- | mysql-test/t/temp_table_symlink.test | 32 | ||||
| -rw-r--r-- | sql/sql_select.cc | 3 | ||||
| -rw-r--r-- | storage/innobase/row/row0sel.cc | 33 | ||||
| -rw-r--r-- | storage/maria/ma_create.c | 6 | ||||
| -rw-r--r-- | storage/myisam/mi_create.c | 6 | ||||
| -rw-r--r-- | storage/xtradb/row/row0sel.cc | 32 |
17 files changed, 547 insertions, 686 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..ef297e5c6b5 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,60 @@ select @cluster_lookups_avoided; @cluster_lookups_avoided 0 DROP TABLE t1; -set global innodb_prefix_index_cluster_optimization = OFF; +# +# MDEV-20464 Division by 0 in row_search_with_covering_prefix() +# +CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1), +KEY (f1,f2,f3)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES (NULL),(0); +SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1; +f1 MAX(f3) COUNT(f4) +NULL NULL 0 +0 0 1 +DROP TABLE t1; +# +# 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 1ab44135f90..c7a5b1a80ca 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 # diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 97961b49f71..1daf523e052 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3356,6 +3356,22 @@ SET sort_buffer_size= @save_sort_buffer_size; SET max_length_for_sort_data= @save_max_length_for_sort_data; DROP TABLE t1; # +# MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)); +INSERT INTO t1 VALUES (0, 1),(1, 2); +CREATE TABLE t2 SELECT * FROM t1; +EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 index PRIMARY b 5 NULL 1 Using where +SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; +c +1 +NULL +DROP TABLE t1,t2; +# end of 10.1 tests +# # MDEV-13994: Bad join results with orderby_uses_equalities=on # CREATE TABLE books ( diff --git a/mysql-test/r/temp_table_symlink.result b/mysql-test/r/temp_table_symlink.result new file mode 100644 index 00000000000..1c5c68170ff --- /dev/null +++ b/mysql-test/r/temp_table_symlink.result @@ -0,0 +1,13 @@ +create table d1 (a int); +create temporary table t1 (a int); +create temporary table t2 (a int); +Got one of the listed errors +create temporary table t3 (a int) engine=Aria; +Got one of the listed errors +select * from information_schema.columns where table_schema='test'; +Got one of the listed errors +flush tables; +select * from d1; +a +drop temporary table t1; +drop table d1; diff --git a/mysql-test/t/fast_prefix_index_fetch_innodb.test b/mysql-test/t/fast_prefix_index_fetch_innodb.test index c3b3440d82d..1987416ff87 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,58 @@ select @cluster_lookups; select @cluster_lookups_avoided; DROP TABLE t1; -set global innodb_prefix_index_cluster_optimization = OFF; + +--echo # +--echo # MDEV-20464 Division by 0 in row_search_with_covering_prefix() +--echo # +CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1), + KEY (f1,f2,f3)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES (NULL),(0); +SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1; +DROP TABLE t1; + +--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 8def57ad377..f5edd736490 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 # diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index fb45ac0e66f..f6886d6d45c 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2201,6 +2201,21 @@ SET max_length_for_sort_data= @save_max_length_for_sort_data; DROP TABLE t1; --echo # +--echo # MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)); +INSERT INTO t1 VALUES (0, 1),(1, 2); +CREATE TABLE t2 SELECT * FROM t1; + +EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; +SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; + +DROP TABLE t1,t2; + +--echo # end of 10.1 tests + +--echo # --echo # MDEV-13994: Bad join results with orderby_uses_equalities=on --echo # diff --git a/mysql-test/t/temp_table_symlink.test b/mysql-test/t/temp_table_symlink.test new file mode 100644 index 00000000000..9297b472805 --- /dev/null +++ b/mysql-test/t/temp_table_symlink.test @@ -0,0 +1,32 @@ +source include/not_windows.inc; + +# +# MDEV-23569 temporary tables can overwrite existing files +# + +let datadir=`select @@datadir`; +create table d1 (a int); +create temporary table t1 (a int); +perl; +chdir "$ENV{MYSQL_TMP_DIR}/mysqld.1/"; +for (<#sql*.MYI>) { + /^#sql(.*)_([0-9a-f]+_)([0-9a-f]+)\.MYI$/ or die; + symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MYI", hex($3)+1; + symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+1; + symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+2; + symlink "$ENV{datadir}/test/d1.MYI", "#sql_$1_0.MAI"; +} +EOF + +error 1,1030; +create temporary table t2 (a int); +error 1,1030; +create temporary table t3 (a int) engine=Aria; +error 1,1030; +select * from information_schema.columns where table_schema='test'; + +flush tables; +select * from d1; +drop temporary table t1; +remove_files_wildcard $MYSQL_TMP_DIR/mysqld.1 *sql*; +drop table d1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 96e9602d77d..19a9cdad184 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21924,6 +21924,9 @@ check_reverse_order: else if (select && select->quick) select->quick->need_sorted_output(); + tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? + join_read_key_unlock_row : rr_unlock_row; + } // QEP has been modified /* diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 7fd0210dbab..ae168ad5130 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -4169,6 +4169,10 @@ bool row_search_with_covering_prefix( const dict_index_t* index = prebuilt->index; ut_ad(!dict_index_is_clust(index)); + if (dict_index_is_spatial(index)) { + return false; + } + if (!srv_prefix_index_cluster_optimization) { return false; } @@ -4179,9 +4183,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) { @@ -4191,33 +4202,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/maria/ma_create.c b/storage/maria/ma_create.c index bca7ad94f85..dc041b79f06 100644 --- a/storage/maria/ma_create.c +++ b/storage/maria/ma_create.c @@ -318,7 +318,7 @@ int maria_create(const char *name, enum data_file_type datafile_type, { options|= HA_OPTION_TMP_TABLE; tmp_table= TRUE; - create_mode|= O_NOFOLLOW; + create_mode|= O_NOFOLLOW | (internal_table ? 0 : O_EXCL); /* "CREATE TEMPORARY" tables are not crash-safe (dropped at restart) */ ci->transactional= FALSE; flags&= ~HA_CREATE_PAGE_CHECKSUM; @@ -886,8 +886,8 @@ int maria_create(const char *name, enum data_file_type datafile_type, { char *iext= strrchr(name, '.'); int have_iext= iext && !strcmp(iext, MARIA_NAME_IEXT); - fn_format(kfilename, name, "", MARIA_NAME_IEXT, - MY_UNPACK_FILENAME | MY_RETURN_REAL_PATH | + fn_format(kfilename, name, "", MARIA_NAME_IEXT, MY_UNPACK_FILENAME | + (internal_table ? 0 : MY_RETURN_REAL_PATH) | (have_iext ? MY_REPLACE_EXT : MY_APPEND_EXT)); /* Replace the current file. diff --git a/storage/myisam/mi_create.c b/storage/myisam/mi_create.c index 9f17a9b2de9..de516d9fb6a 100644 --- a/storage/myisam/mi_create.c +++ b/storage/myisam/mi_create.c @@ -184,7 +184,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs, if (flags & HA_CREATE_TMP_TABLE) { options|= HA_OPTION_TMP_TABLE; - create_mode|= O_NOFOLLOW; + create_mode|= O_NOFOLLOW | (internal_table ? 0 : O_EXCL); } if (flags & HA_CREATE_CHECKSUM || (options & HA_OPTION_CHECKSUM)) { @@ -619,8 +619,8 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs, { char *iext= strrchr(name, '.'); int have_iext= iext && !strcmp(iext, MI_NAME_IEXT); - fn_format(kfilename, name, "", MI_NAME_IEXT, - MY_UNPACK_FILENAME | MY_RETURN_REAL_PATH | + fn_format(kfilename, name, "", MI_NAME_IEXT, MY_UNPACK_FILENAME | + (internal_table ? 0 : MY_RETURN_REAL_PATH) | (have_iext ? MY_REPLACE_EXT : MY_APPEND_EXT)); /* Replace the current file */ create_flag=(flags & HA_CREATE_KEEP_FILES) ? 0 : MY_DELETE_OLD; 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; |