How do I rename an Index in MySQL

Question

I would like to rename an index. I've looked at the alter table documentation, but I can't figure out the syntax to simply rename an index. When doing it through the MySQL GUI, it drops the index, and creates a new one. While this works, I would like to avoid rebuilding the entire index just to change the name of an index.

[ADDITIONAL INFO]

In the alter table documentation it states

Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:

* Renaming a column or index.

However, when I tried to rename the index by editing the .frm file (on a test database) and restarting the server, it now states "Could not fetch columns" in the UI when trying to list the columns, and when trying to run a query, it returns the error "Unknown table engine ''". The .frm file has a lot of binary content. Is there a good tool for editing the binary info.

Answer 1

I answered this question in 2009. At that time there was no syntax in MySQL to rename an index.

Since then, MySQL 5.7 introduced an ALTER TABLE RENAME INDEX syntax.

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html says in part:

• RENAME INDEX old_index_name TO new_index_name renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged. old_index_name must be the name of an existing index in the table that is not dropped by the same ALTER TABLE statement. new_index_name is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can be PRIMARY.

Earlier versions of MySQL, e.g. 5.6 and earlier, support no syntax in ALTER TABLE to rename an index (or key, which is a synonym).

The only solution was to ALTER TABLE DROP KEY oldkeyname, ADD KEY newkeyname (...).

There is no ALTER INDEX command in MySQL. You can only DROP INDEX and then CREATE INDEX with the new name.

---

Regarding your update above: perhaps the documentation isn't precise enough. Regardless, there's no SQL syntax to rename an index.

An index is a data structure that can be rebuilt from the data (in fact it's recommended to rebuild indexes periodically with OPTIMIZE TABLE). It takes some time, but it's a commonplace operation. Indexes data structures are separate from table data, so adding or dropping an index shouldn't need to touch the table data, as the documentation says.

Regarding the .frm file, MySQL does not support editing the .frm file. I wouldn't do it for any reason. You are 100% guaranteed to corrupt your table and make it unusable.

---

Answer 2

For MySQL 5.7:

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name

For MySQL older versions:

ALTER TABLE tbl_name DROP INDEX old_index_name, ADD INDEX new_index_name (...)

See http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

Answer 3

This question was asked ages ago, and was last updated over half a year ago. Still I feel the need to add this tip:

If the indexed column is used elsewhere as a foreign key, you may encounter an error related to that. Doing this may help:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE tbl DROP INDEX index_name;
ALTER TABLE tbl ADD INDEX new_index_name (indexed_column);
SET FOREIGN_KEY_CHECKS = 1;

Hope someone finds this useful.