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

MySQL supports no syntax in ALTER TABLE to rename an index (or key, which is a synonym).

You can ALTER TABLE DROP KEY and ALTER TABLE ADD KEY.

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.