Details
Trivial Description
The "disabled" flag set on an index by ALTER TABLE ... DISABLED KEYS is persistent through server restarts, but it is not shown in SHOW CREATE TABLE. Consequently, mysqldump does not store it (and even if it did, it does DISABLE KEYS / ENABLE KEYS of its own, so it would have removed it anyway). Thus, the attribute is lost after restoring the dump.
CREATE TABLE t (a INT, KEY(a)) ENGINE=MyISAM; |
INSERT INTO t VALUES (1),(2),(3),(4),(5); |
EXPLAIN SELECT * FROM t WHERE a = 2; |
ALTER TABLE t DISABLE KEYS; |
SHOW CREATE TABLE t; |
EXPLAIN SELECT * FROM t WHERE a = 2; |
--source include/restart_mysqld.inc
|
|
|
EXPLAIN SELECT * FROM t WHERE a = 2; |
SELECT INDEX_NAME, COMMENT FROM INFORMATION_SCHEMA.STATISTICS |
WHERE TABLE_NAME = 't'; |
--exec $MYSQL_DUMP test --tables t > $MYSQL_TMP_DIR/t.dump
|
DROP TABLE t; |
--exec $MYSQL test < $MYSQL_TMP_DIR/t.dump
|
EXPLAIN SELECT * FROM t WHERE a = 2; |
SELECT INDEX_NAME, COMMENT FROM INFORMATION_SCHEMA.STATISTICS |
WHERE TABLE_NAME = 't'; |
|
|
DROP TABLE t; |
|
main f4a85225ea17b2c77c20e761773090a08aa3eceb |
CREATE TABLE t (a INT, KEY(a)) ENGINE=MyISAM; |
INSERT INTO t VALUES (1),(2),(3),(4),(5); |
EXPLAIN SELECT * FROM t WHERE a = 2; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t ref a a 5 const 1 Using index |
ALTER TABLE t DISABLE KEYS; |
SHOW CREATE TABLE t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
KEY `a` (`a`) |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
EXPLAIN SELECT * FROM t WHERE a = 2; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t ALL NULL NULL NULL NULL 5 Using where |
# restart
|
EXPLAIN SELECT * FROM t WHERE a = 2; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t ALL NULL NULL NULL NULL 5 Using where |
SELECT INDEX_NAME, COMMENT FROM INFORMATION_SCHEMA.STATISTICS |
WHERE TABLE_NAME = 't'; |
INDEX_NAME COMMENT
|
a disabled
|
DROP TABLE t; |
EXPLAIN SELECT * FROM t WHERE a = 2; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t ref a a 5 const 1 Using index |
SELECT INDEX_NAME, COMMENT FROM INFORMATION_SCHEMA.STATISTICS |
WHERE TABLE_NAME = 't'; |
INDEX_NAME COMMENT
|
a
|
DROP TABLE t; |