ignore_indexes.result 18.1 KB
Newer Older
1 2 3 4 5 6 7 8
#
# MDEV-7317: Make an index ignorable to the optimizer
#
# Test of ALTER INDEX syntax.
CREATE TABLE t1 ( a INT, KEY (a) );
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	Ignored
t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
9
ALTER TABLE t1 ALTER INDEX a IGNORED;
10 11 12
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	Ignored
t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
13
ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
14 15 16 17
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	Ignored
t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
DROP TABLE t1;
18
# Test of CREATE INDEX syntax with IGNORED indexes.
19
CREATE TABLE t1 ( a INT, b INT );
20 21
CREATE INDEX a_ignorable ON t1(a) IGNORED;
CREATE INDEX b_not_ignorable ON t1(a) NOT IGNORED;
22 23
Warnings:
Note	1831	Duplicate index `b_not_ignorable`. This is deprecated and will be disallowed in a future release
24
CREATE INDEX a_b_ignorable ON t1(a, b) IGNORED;
25 26 27 28 29 30 31
SHOW INDEXES 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_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
t1	1	b_not_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
t1	1	a_b_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
t1	1	a_b_ignorable	2	b	A	NULL	NULL	NULL	YES	BTREE			YES
DROP TABLE t1;
32
# Test that IGNORED indexes are not used.
33
CREATE TABLE t1 ( a INT, KEY (a) );
34
CREATE TABLE t2 ( a INT, KEY (a) IGNORED );
35 36 37 38 39 40 41 42 43 44 45
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
INSERT INTO t2 SELECT * FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	5	Using index
46
ALTER TABLE t1 ALTER INDEX a IGNORED;
47 48 49
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
50
ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
51 52 53 54 55 56
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	5	Using index
EXPLAIN SELECT a FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	
57
ALTER TABLE t2 ALTER INDEX a NOT IGNORED;
58 59 60 61 62 63 64
EXPLAIN SELECT a FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	a	5	NULL	5	Using index
DROP TABLE t1, t2;
# Test that renaming an index does not change ignorability and vice versa.
CREATE TABLE t1 (
a INT, INDEX (a),
65
b INT, INDEX (b) IGNORED
66 67 68 69 70 71 72 73 74 75 76
);
SHOW INDEXES 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	NULL	NULL	NULL	YES	BTREE			NO
t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
ALTER TABLE t1 RENAME INDEX a TO a1;
ALTER TABLE t1 RENAME INDEX b TO b1;
SHOW INDEXES 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	a1	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
t1	1	b1	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
77 78
ALTER TABLE t1 ALTER INDEX a1 IGNORED;
ALTER TABLE t1 ALTER INDEX b1 NOT IGNORED;
79 80 81 82 83 84 85 86 87 88
SHOW INDEXES 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	a1	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
t1	1	b1	1	b	A	NULL	NULL	NULL	YES	BTREE			NO
DROP TABLE t1;
# Test of SHOW CREATE TABLE.
CREATE TABLE t1 (
a INT,
b INT,
c INT,
89 90
INDEX (a) NOT IGNORED,
INDEX (b) IGNORED,
91 92 93 94 95 96 97 98 99 100 101 102 103 104
INDEX (c)
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`) IGNORED,
  KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
# Test that primary key indexes can't be made ignorable.
105
CREATE TABLE t1 ( a INT, PRIMARY KEY (a) IGNORED );
106
ERROR HY000: A primary key cannot be marked as IGNORE
107 108 109 110 111 112
CREATE TABLE t1 ( a INT PRIMARY KEY IGNORED );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IGNORED )' at line 1
CREATE TABLE t1 ( a INT KEY IGNORED );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IGNORED )' at line 1
ALTER TABLE t1 ALTER INDEX PRIMARY IGNORED;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PRIMARY IGNORED' at line 1
113
CREATE TABLE t1(a INT NOT NULL);
114
ALTER TABLE t1 ADD PRIMARY KEY (a) IGNORED;
115 116 117 118
ERROR HY000: A primary key cannot be marked as IGNORE
DROP TABLE t1;
CREATE TABLE t1 (
a INT, KEY (a),
119
b INT, KEY (b) IGNORED
120 121 122
);
ALTER TABLE t1 RENAME INDEX no_such_index TO x;
ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
123
ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
124 125 126 127
ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
DROP TABLE t1;
CREATE TABLE t1 (
a INT, KEY (a),
128
b INT, KEY (b) IGNORED
129 130 131
);
ALTER TABLE t1 RENAME INDEX no_such_index TO x;
ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
132
ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
133 134 135 136
ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
#
# Repeated alter actions. Should work.
#
137
ALTER TABLE t1 ALTER INDEX a IGNORED, ALTER INDEX a NOT IGNORED;
138 139 140 141
SHOW INDEXES 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	NULL	NULL	NULL	YES	BTREE			NO
t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
142
ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALTER INDEX b IGNORED;
143 144 145 146 147
SHOW INDEXES 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	NULL	NULL	NULL	YES	BTREE			NO
t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
#
148
# Various combinations of RENAME INDEX and ALTER INDEX ... IGNORED.
149 150 151
#
ALTER TABLE t1 RENAME INDEX a TO x, RENAME INDEX x TO a;
ERROR 42000: Key 'x' doesn't exist in table 't1'
152
ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX x IGNORED;
153
ERROR 42000: Key 'x' doesn't exist in table 't1'
154
ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX a NOT IGNORED;
155
ERROR 42000: Key 'a' doesn't exist in table 't1'
156
ALTER TABLE t1 ALTER INDEX a NOT IGNORED, RENAME INDEX a TO x;
157 158 159 160 161 162 163 164 165
ERROR 42000: Key 'a' doesn't exist in table 't1'
#
# Various algorithms and their effects.
#
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
166
ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = COPY;
167 168 169 170 171 172 173 174 175 176
affected rows: 3
info: Records: 3  Duplicates: 0  Warnings: 0
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
SHOW INDEXES 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	3	NULL	NULL	YES	BTREE			YES
t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
177
ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALGORITHM = INPLACE;
178 179 180 181 182 183 184 185 186 187
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
SHOW INDEXES 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	3	NULL	NULL	YES	BTREE			NO
t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
188
ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = DEFAULT;
189 190 191 192 193 194 195 196 197 198
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
SHOW INDEXES 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	3	NULL	NULL	YES	BTREE			YES
t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
199
ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
200 201 202 203 204 205 206 207 208 209
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
SHOW INDEXES 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	3	NULL	NULL	YES	BTREE			NO
t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
210
ALTER TABLE t1 ADD INDEX ab(a, b), ALTER INDEX ab IGNORED;
211 212 213 214 215 216 217 218 219
ERROR 42000: Key 'ab' doesn't exist in table 't1'
DROP TABLE t1;
#
# The first NOT NULL UNIQUE index may of course be IGNORED if it is
# not promoted to a primary key
#
CREATE TABLE t1 (
a INT NOT NULL,
b INT NOT NULL PRIMARY KEY,
220
UNIQUE KEY (a) IGNORED
221 222 223 224 225 226 227 228 229 230 231
);
SHOW INDEXES 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	0	PRIMARY	1	b	A	0	NULL	NULL		BTREE			NO
t1	0	a	1	a	A	0	NULL	NULL		BTREE			YES
DROP TABLE t1;
# The check above applies only to the first NOT NULL UNIQUE index.
CREATE TABLE t1 (
a INT NOT NULL,
b INT NOT NULL,
UNIQUE KEY (a),
232
UNIQUE KEY (b) IGNORED
233 234 235 236 237 238
);
SHOW INDEXES 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	0	a	1	a	A	0	NULL	NULL		BTREE			NO
t1	0	b	1	b	A	0	NULL	NULL		BTREE			YES
DROP TABLE t1;
239
CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY (a) IGNORED);
240
ERROR HY000: A primary key cannot be marked as IGNORE
241
CREATE TEMPORARY TABLE t1 ( a INT, KEY (a) IGNORED);
242 243 244 245 246 247 248
INSERT INTO t1 VALUES (0), (1), (2), (3);
SHOW INDEXES 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	NULL	NULL	NULL	YES	BTREE			YES
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
249
ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
250 251 252 253 254
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	4	Using index
DROP TABLE t1;
#
255
# IGNORED fulltext indexes.
256 257 258 259 260 261 262 263 264 265 266
#
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
INSERT INTO t1 VALUES('Some data', 'for full-text search');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
test.t1	analyze	status	OK
EXPLAIN SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
267
ALTER TABLE t1 ALTER INDEX a IGNORED;
268 269 270 271
EXPLAIN SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ("collections");
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
#
272
# IGNORED indexes on AUTO_INCREMENT columns.
273 274 275 276 277 278 279 280 281 282
#
CREATE TABLE t1 ( a INT AUTO_INCREMENT, KEY ( a ) );
INSERT INTO t1 VALUES (), (), ();
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	4	NULL	3	Using index
283
ALTER TABLE t1 ALTER INDEX a IGNORED;
284 285 286 287 288 289 290 291
SHOW INDEXES 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	3	NULL	NULL		BTREE			YES
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
DROP TABLE t1;
#
292
# IGNORED spatial indexes
293 294 295 296 297 298 299 300 301 302
#
CREATE TABLE t1 (
fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
g GEOMETRY NOT NULL,
SPATIAL KEY key1(g)
);
EXPLAIN SELECT fid, AsText(g) FROM t1
WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	key1	key1	34	NULL	8	Using where
303
ALTER TABLE t1 ALTER INDEX key1 IGNORED;
304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320
SHOW INDEXES 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	0	PRIMARY	1	fid	A	150	NULL	NULL		BTREE			NO
t1	1	key1	1	g	A	NULL	32	NULL		SPATIAL			YES
EXPLAIN SELECT fid, AsText(g) FROM t1
WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	150	Using where
DROP TABLE t1;
CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY key1(a));
INSERT INTO t1 VALUES (),(),();
SHOW INDEXES 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	key1	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	key1	5	NULL	3	Using index
321
ALTER TABLE t1 ALTER INDEX key1 IGNORED;
322 323 324 325 326 327 328 329
SHOW INDEXES 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	key1	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
DROP TABLE t1;
#
330
# Partitioning on keys with an IGNORED index, IGNORED indexes over
331 332 333 334 335 336 337 338
# partitioned tables.
#
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,
339
KEY (a) IGNORED,
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369
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;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	14	
EXPLAIN SELECT b FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	b	2	NULL	14	Using index
EXPLAIN SELECT * FROM t1 WHERE a = '04';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
370
ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
371 372 373 374 375 376
EXPLAIN SELECT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	2	NULL	14	Using index
EXPLAIN SELECT * FROM t1 WHERE a = '04';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	2	const	2	Using where
377
ALTER TABLE t1 ALTER INDEX b IGNORED;
378 379 380 381 382
EXPLAIN SELECT b FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	14	
DROP TABLE t1;
#
383
# Using FORCE INDEX for an IGNORED index
384 385 386 387 388 389
#
CREATE TABLE t1(a INT, key k1(a));
INSERT INTO t1 VALUES (1),(2),(3);
EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	k1	5	NULL	3	Using index
390
ALTER TABLE t1 ALTER INDEX k1 IGNORED;
391 392 393 394 395 396 397 398 399
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `k1` (`a`) IGNORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
ERROR 42000: Key 'k1' doesn't exist in table 't1'
DROP TABLE t1;
400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460
#
# MDEV-25075: Ignorable index makes the resulting CREATE TABLE invalid
#
CREATE TABLE t1 (a INT, KEY (a));
ALTER TABLE t1 ALTER INDEX a IGNORED;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) IGNORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 ( a INT, KEY (a) IGNORED);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) IGNORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
#
# Tests to check usage of IGNORED keyword
#
CREATE TABLE IGNORED(a INT);
DROP TABLE IGNORED;
CREATE TABLE t1(a INT);
SELECT * FROM t1 IGNORED;
a
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
DECLARE IGNORED INT DEFAULT 0;
RETURN 0;
END|
CREATE FUNCTION f2(a INT) RETURNS INT
BEGIN
DECLARE IGNORED INT DEFAULT 0;
DECLARE x INT DEFAULT 0;
SET x= IGNORED;
RETURN 0;
END|
DROP TABLE t1;
DROP FUNCTION f1;
DROP FUNCTION f2;
CREATE PROCEDURE test_sp()
BEGIN
ignored:
LOOP
LEAVE ignored;
END LOOP;
END|
DROP PROCEDURE test_sp;
CREATE PROCEDURE test_sp()
BEGIN
set @@ignored= 1;
END|
ERROR HY000: Unknown system variable 'ignored'
CREATE PROCEDURE proc()
BEGIN
SET IGNORED= a+b;
END |
ERROR HY000: Unknown system variable 'IGNORED'
461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481
#
# ALLOWING ALTER KEY syntax in ALTER TABLE
#
CREATE TABLE t1 (a INT, KEY (a));
ALTER TABLE t1 ALTER INDEX a IGNORED;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) IGNORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (a INT, KEY (a));
ALTER TABLE t1 ALTER KEY a IGNORED;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) IGNORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;