Bug #5483 "show create index" would be nice...

• Submitted: 8 Sep 2004 21:43
• Modified: 23 Jan 2006 11:47
• Reporter: Nick Sullivan
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• CPU Architecture: Any

[8 Sep 2004 21:43] Nick Sullivan

Description:
I love "show create table", it's very handy if you move data around a lot from server to server, continually rebuilding/recreating tables.

A similar feature would be great - " show create index" that showed you the appropriate "create index " statements for a given table.

How to repeat:
mysql>show create index from table;

create index foo on sales(dollar);
create unique index foo2 on sales(accountid);
create fulltext index foo on sales(Notes);

Suggested fix:
mysql>show create index from table;

create index foo on sales(dollar);
create unique index foo2 on sales(accountid);
create fulltext index foo on sales(Notes);

[23 Jan 2006 11:47] Valeriy Kravchuk

Thank you for a feature request. Sorry, but I do not think this feature in needed, because SHOW CREATE TABLE already shows all the indexes (keys), and CREATE INDEX is not the only way to add new indexes.

[22 Feb 2019 12:23] Wim Roffel

I support this request. I support a software package with thousands of installations and one of the problems that I see repeatedly arising is tables without an index. 

It is not that hard to get it: in order to move the application from one server to another you export the tables and then import them again. But your sql file is too big. So you export the missing files in a second sql file and import them that way. It is easy to forget that in a sql file the indexes are set at the bottom so that your first batch won't have indexes in this scenario.

A command like this would help greatly to test for this kind of problems and to fix them.