From f691d9865becfd242ba44cc632034433336af1e7 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Thu, 25 Feb 2021 19:59:51 +0530 Subject: MDEV-7317: Make an index ignorable to the optimizer This feature adds the functionality of ignorability for indexes. Indexes are not ignored be default. To control index ignorability explicitly for a new index, use IGNORE or NOT IGNORE as part of the index definition for CREATE TABLE, CREATE INDEX, or ALTER TABLE. Primary keys (explicit or implicit) cannot be made ignorable. The table INFORMATION_SCHEMA.STATISTICS get a new column named IGNORED that would store whether an index needs to be ignored or not. --- mysql-test/main/mix2_myisam.result | 30 +++++++++++++++--------------- 1 file changed, 15 insertions(+), 15 deletions(-) (limited to 'mysql-test/main/mix2_myisam.result') diff --git a/mysql-test/main/mix2_myisam.result b/mysql-test/main/mix2_myisam.result index 5a228f63502..dd2af645767 100644 --- a/mysql-test/main/mix2_myisam.result +++ b/mysql-test/main/mix2_myisam.result @@ -170,10 +170,10 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 id A # NULL NULL BTREE -t1 1 parent_id 1 parent_id A # NULL NULL BTREE -t1 1 level 1 level A # NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 id A # NULL NULL BTREE NO +t1 1 parent_id 1 parent_id A # NULL NULL BTREE NO +t1 1 level 1 level A # NULL NULL BTREE NO drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, @@ -214,8 +214,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 skr 1 a A # NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 skr 1 a A # NULL NULL YES BTREE NO drop table t1; create table t1 (a int,b varchar(20),key(a)) engine=MyISAM; insert into t1 values (1,""), (2,"testing"); @@ -261,13 +261,13 @@ key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM; Warnings: Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A # NULL NULL BTREE -t1 0 PRIMARY 2 b A # NULL NULL BTREE -t1 0 c 1 c A # NULL NULL BTREE -t1 0 b 1 b A # NULL NULL BTREE -t1 1 a 1 a A # NULL NULL BTREE -t1 1 a_2 1 a A # NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A # NULL NULL BTREE NO +t1 0 PRIMARY 2 b A # NULL NULL BTREE NO +t1 0 c 1 c A # NULL NULL BTREE NO +t1 0 b 1 b A # NULL NULL BTREE NO +t1 1 a 1 a A # NULL NULL BTREE NO +t1 1 a_2 1 a A # NULL NULL BTREE NO drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY; alter table t1 engine=MyISAM; @@ -639,8 +639,8 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A # NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A # NULL NULL BTREE NO drop table t1; create table t1 (i int, j int ) ENGINE=MyISAM; insert into t1 values (1,2); -- cgit v1.2.1