mirror of
https://github.com/MariaDB/server.git
synced 2025-11-25 15:09:44 +01:00
5f845854fb
Remove the FIX_INDEX_STATS_FOR_ALL_NULLS flag from @@new_mode. The new behavior is now always on.
146 lines
9.3 KiB
Text
146 lines
9.3 KiB
Text
# Small driving table
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2000),(3,300);
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# Table that will be accessed by an index lookup (`ref` access)
|
|
CREATE TABLE t2 (a INT, b INT, KEY key_b(b));
|
|
# All t11.b values are NULL
|
|
INSERT INTO t2 SELECT seq/100, NULL FROM seq_1_to_1000;
|
|
ANALYZE TABLE t2 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
# NULL-rejecting equality t1.b = t2.b will not return any matches
|
|
# because all values of t2.b are NULL. So "rows" = 1 for t2 where 1 is
|
|
# a special value meaning "very few" rows
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t1.b = t2.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t2 ref key_b key_b 5 test.t1.b 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b`
|
|
# However, rows estimation for not NULL-rejecting conditions
|
|
# must not be affected ("rows" > 1 is expected)
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t1.b <=> t2.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ref key_b key_b 5 test.t1.b 11 100.00 Using index condition; Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <=> `test`.`t2`.`b`
|
|
# Insert some non-NULL values and re-collect the stats
|
|
INSERT INTO t2 SELECT 1, 1 FROM seq_1_to_100;
|
|
ANALYZE TABLE t2 PERSISTENT FOR COLUMNS (b) INDEXES (key_b);
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t1.b = t2.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t2 ref key_b key_b 5 test.t1.b 100 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b`
|
|
# Test composite index for two columns. Key prefix is used for access
|
|
CREATE TABLE t3 (a INT, b INT, KEY key_ab(a,b));
|
|
# All t3.b values are NULL
|
|
INSERT INTO t3 SELECT seq/100, NULL FROM seq_1_to_1000;
|
|
ANALYZE TABLE t3 PERSISTENT FOR COLUMNS(b) INDEXES(key_ab);
|
|
Table Op Msg_type Msg_text
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status Table is already up to date
|
|
# NULL-rejecting equality t1.b = t3.b, same as above.
|
|
# "rows" must be estimated to 1
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t3 ON t1.a = t3.a AND t1.b = t3.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t3 ref key_ab key_ab 10 test.t1.a,test.t1.b 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`b`
|
|
# Rows estimation for not NULL-rejecting conditions are not affected
|
|
# ("rows" > 1 is expected)
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t3 ON t1.a = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t3 ref key_ab key_ab 5 test.t1.a 90 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`a`
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t3 ON t1.a = t3.a AND t1.b <=> t3.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t3 ref key_ab key_ab 10 test.t1.a,test.t1.b 11 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <=> `test`.`t3`.`b`
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t3 ON t1.a = t3.a AND t3.b is NULL;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t3 ref key_ab key_ab 10 test.t1.a,const 11 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` is null
|
|
# Insert some non-NULL values and re-collect the stats
|
|
INSERT INTO t3 SELECT 1, 1 FROM seq_1_to_100;
|
|
ANALYZE TABLE t3 PERSISTENT FOR COLUMNS (b) INDEXES (key_ab);
|
|
Table Op Msg_type Msg_text
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t3 ON t1.a = t3.a AND t1.b = t3.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t3 ref key_ab key_ab 10 test.t1.a,test.t1.b 100 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`b`
|
|
# Test composite index for 3 columns. Key prefix is used for access
|
|
CREATE TABLE t4 (a INT, b INT, c INT, KEY key_abc(a,b,c));
|
|
# All t3.b values are NULL
|
|
INSERT INTO t4 SELECT seq/10, NULL, seq/10 FROM seq_1_to_1000;
|
|
ANALYZE TABLE t4 PERSISTENT FOR COLUMNS(b) INDEXES(key_abc);
|
|
Table Op Msg_type Msg_text
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status Table is already up to date
|
|
# NULL-rejecting equality t1.b = t3.b, same as above.
|
|
# "rows" must be estimated to 1
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t4 ON t1.a = t4.a AND t1.b = t4.b;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t4 ref key_abc key_abc 10 test.t1.a,test.t1.b 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t1` join `test`.`t4` where `test`.`t4`.`a` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t1`.`b`
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t4 ON t1.a = t4.a AND t1.b = t4.b and t1.b = t4.c;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t4 ref key_abc key_abc 15 test.t1.a,test.t1.b,test.t1.b 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t1` join `test`.`t4` where `test`.`t4`.`a` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t1`.`b` and `test`.`t4`.`c` = `test`.`t1`.`b`
|
|
# "rows" expected to be > 1
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t4 ON t1.a = t4.a;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t4 ref key_abc key_abc 5 test.t1.a 9 100.00 Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t1` join `test`.`t4` where `test`.`t4`.`a` = `test`.`t1`.`a`
|
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t4 ON t1.a = t4.a AND t1.b <=> t4.c;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE t4 ref key_abc key_abc 5 test.t1.a 9 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t1` join `test`.`t4` where `test`.`t4`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <=> `test`.`t4`.`c`
|
|
DROP TABLE t1, t2, t3, t4;
|
|
# Test for partially covered column
|
|
CREATE TABLE t1 (a VARCHAR(10));
|
|
INSERT INTO t1 SELECT seq FROM seq_1_to_10;
|
|
CREATE TABLE t2 (
|
|
a VARCHAR(10),
|
|
b VARCHAR(10),
|
|
INDEX i1(a, b(5))
|
|
);
|
|
INSERT INTO t2 SELECT seq, NULL FROM seq_1_to_1000;
|
|
ANALYZE TABLE t2 PERSISTENT FOR COLUMNS (b) INDEXES (i1);
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
EXPLAIN SELECT * FROM t1, t2 WHERE t2.a=t1.a AND t2.b=t1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
1 SIMPLE t2 ref i1 i1 66 test.t1.a,test.t1.a 1 Using where
|
|
DROP TABLE t1, t2;
|