mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
f691d9865b
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.
96 lines
3.6 KiB
Plaintext
96 lines
3.6 KiB
Plaintext
'#--------------------FN_DYNVARS_097_01-------------------------#'
|
|
SET @start_value = @@global.myisam_stats_method;
|
|
SET @@global.myisam_stats_method = nulls_equal;
|
|
connect con1,localhost,root,,,,;
|
|
connection con1;
|
|
SELECT @@global.myisam_stats_method;
|
|
@@global.myisam_stats_method
|
|
NULLS_EQUAL
|
|
SELECT @@session.myisam_stats_method;
|
|
@@session.myisam_stats_method
|
|
NULLS_EQUAL
|
|
disconnect con1;
|
|
'#--------------------FN_DYNVARS_097_02-------------------------#'
|
|
connection default;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a INT, KEY (a));
|
|
INSERT INTO t1 VALUES (0),(1),(2),(3),(4);
|
|
INSERT INTO t1 SELECT NULL FROM t1;
|
|
'default: NULLs considered unequal'
|
|
SET myisam_stats_method = nulls_unequal;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 10 NULL NULL YES BTREE NO
|
|
INSERT INTO t1 VALUES (11);
|
|
DELETE FROM t1 WHERE a = 11;
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 10 NULL NULL YES BTREE NO
|
|
'Set nulls to be equal'
|
|
SET myisam_stats_method = nulls_equal;
|
|
set @save_use_stat_tables= @@use_stat_tables;
|
|
set @@use_stat_tables= COMPLEMENTARY;
|
|
INSERT INTO t1 VALUES (11);
|
|
DELETE FROM t1 WHERE a = 11;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 5 NULL NULL YES BTREE NO
|
|
INSERT INTO t1 VALUES (11);
|
|
DELETE FROM t1 WHERE a = 11;
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 5 NULL NULL YES BTREE NO
|
|
'Set nulls to be ignored'
|
|
SET myisam_stats_method = nulls_ignored;
|
|
SHOW VARIABLES LIKE 'myisam_stats_method';
|
|
Variable_name Value
|
|
myisam_stats_method NULLS_IGNORED
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
a CHAR(3), b CHAR(4), c CHAR(5), d CHAR(6),
|
|
KEY(a,b,c,d)
|
|
);
|
|
INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz');
|
|
INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz');
|
|
INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
|
|
INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 2 NULL NULL YES BTREE NO
|
|
t1 1 a 2 b A 4 NULL NULL YES BTREE NO
|
|
t1 1 a 3 c A 4 NULL NULL YES BTREE NO
|
|
t1 1 a 4 d A 4 NULL NULL YES BTREE NO
|
|
DELETE FROM t1;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
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 Ignored
|
|
t1 1 a 1 a A 0 NULL NULL YES BTREE NO
|
|
t1 1 a 2 b A 0 NULL NULL YES BTREE NO
|
|
t1 1 a 3 c A 0 NULL NULL YES BTREE NO
|
|
t1 1 a 4 d A 0 NULL NULL YES BTREE NO
|
|
SET myisam_stats_method = DEFAULT;
|
|
set @@use_stat_tables= @save_use_stat_tables;
|
|
DROP TABLE t1;
|
|
SET @@global.myisam_stats_method= @start_value;
|