MySQL Bugs: #25694: Unique indexes disabled with ALTER TABLE aren't shown as disabled in SHOW KEYS

Bug #25694	Unique indexes disabled with ALTER TABLE aren't shown as disabled in SHOW KEYS
Submitted:	18 Jan 2007 13:47	Modified:	2 May 2007 18:03
Reporter:	Martin Hansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	ALTER TABLE, disable index, disable keys, show indexes, SHOW KEYS

Description:
SHOW KEYS from <table> is inconsistent in showing whether keys are disabled or not. After an ALTER TABLE <table> DISABLE KEYS is issued, non-unique indexes have the comment 'disabled', while unique indexes do not.

How to repeat:
# error, b_index does not get shown as disabled

drop table if exists t1;
create table t1( a int, b int );
create index a_index on t1( a );
create unique index b_index on t1( b );
alter table t1 disable keys;
show keys from t1;

This is actually not just the display of the indexes that is in error. After pasting the above commands, try the following:

mysql> explain select * from t2 where b=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t2    | const | b_index       | b_index | 5       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

Hence, the key is not disabled.

The MySQL 5.1 manual says the following about ALTER TABLE ... DISABLE KEYS:

"If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11. (Bug#19502)"
---
Nothing is said about how ALTER TABLE ... DISABLE KEYS should act with regard to SELECT statements. It is rather unintuitive that only non-unique keys should be disabled this way, even more so when it is not directly documented.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added this para:

While the non-unique indexes are disabled, they are ignored for
statements such as SELECT and EXPLAIN that otherwise would use them.