1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
--source include/invisible_indexes.inc
--echo #
--echo # Tests that don't work on MyISAM ( native partitioning, indexes on
--echo # generated columns, etc.)
--echo #
--echo #
--echo # Partitioning on keys with an invisible index, invisible indexes over
--echo # partitioned tables.
--echo #
CREATE TABLE t1 (
a CHAR(2) NOT NULL,
b CHAR(2) NOT NULL,
c INT(10) UNSIGNED NOT NULL,
d VARCHAR(255) DEFAULT NULL,
e VARCHAR(1000) DEFAULT NULL,
KEY (a) INVISIBLE,
KEY (b)
) PARTITION BY KEY (a) PARTITIONS 20;
INSERT INTO t1 (a, b, c, d, e) VALUES
('07', '03', 343, '1', '07_03_343'),
('01', '04', 343, '2', '01_04_343'),
('01', '06', 343, '3', '01_06_343'),
('01', '07', 343, '4', '01_07_343'),
('01', '08', 343, '5', '01_08_343'),
('01', '09', 343, '6', '01_09_343'),
('03', '03', 343, '7', '03_03_343'),
('03', '06', 343, '8', '03_06_343'),
('03', '07', 343, '9', '03_07_343'),
('04', '03', 343, '10', '04_03_343'),
('04', '06', 343, '11', '04_06_343'),
('05', '03', 343, '12', '05_03_343'),
('11', '03', 343, '13', '11_03_343'),
('11', '04', 343, '14', '11_04_343');
ANALYZE TABLE t1;
EXPLAIN SELECT a FROM t1;
EXPLAIN SELECT b FROM t1;
EXPLAIN SELECT * FROM t1 WHERE a = '04';
ALTER TABLE t1 ALTER INDEX a VISIBLE;
EXPLAIN SELECT a FROM t1;
EXPLAIN SELECT * FROM t1 WHERE a = '04';
ALTER TABLE t1 ALTER INDEX b INVISIBLE;
EXPLAIN SELECT b FROM t1;
DROP TABLE t1;
CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY (a) INVISIBLE );
SHOW INDEXES FROM t1;
EXPLAIN SELECT a FROM t1;
DROP TABLE t1;
--echo #
--echo # Test that referential constraints implemented by the indexes are still
--echo # enforced while the index is invisible.
--echo #
CREATE TABLE t1p ( a INT KEY );
CREATE TABLE t1c ( t1p_a INT );
ALTER TABLE t1c ADD CONSTRAINT FOREIGN KEY ( t1p_a ) REFERENCES t1p( a );
ALTER TABLE t1c ALTER INDEX t1p_a INVISIBLE;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t1c VALUES ( 1 );
SELECT * FROM t1c;
DROP TABLE t1c, t1p;
--echo #
--echo # Bug#25837038: FEATURE REQUEST : USE INVISIBLE INDEXES SPECIFIC QUERY
--echo #
CREATE TABLE t1 ( a INT, KEY( a ) INVISIBLE );
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
--connect ( con1, localhost, root, , )
--connection con1
--replace_result hypergraph_optimizer=on hypergraph_optimizer=off
SELECT @@optimizer_switch;
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
SET @@optimizer_switch='use_invisible_indexes=on';
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
--connection default
--replace_result hypergraph_optimizer=on hypergraph_optimizer=off
SELECT @@optimizer_switch;
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
--connection con1
--replace_result hypergraph_optimizer=on hypergraph_optimizer=off
SELECT @@optimizer_switch;
SET @@optimizer_switch='use_invisible_indexes=off';
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
--disconnect con1
--connection default
--replace_column 10 X
EXPLAIN SELECT a FROM t1;
DROP TABLE t1;
--echo #
--echo # Bug#31550839 OPTIMIZER SWITCH USE_INVISIBLE_INDEXES=ON DOES NOT ALLOW FORCE INDEX
--echo #
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
b INT NOT NULL,
INDEX (b) INVISIBLE
);
INSERT INTO t1 VALUES (1, 1), (2,2),(3,3),(4,4),(5,5);
ANALYZE TABLE t1;
SET optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM t1 FORCE INDEX (b) WHERE b = 3;
SET optimizer_switch="use_invisible_indexes=default";
--error ER_KEY_DOES_NOT_EXITS
EXPLAIN SELECT * FROM t1 FORCE INDEX (b) WHERE b = 3;
DROP TABLE t1;
|
