Mysql Command For Dropping An Unnamed Index

[grahamgr3]

in the book it shows us how to drop an index by using the following

sql statement. ALTER TABLE t DROP INDEX i

i being the name of the index. I went into phpmyadmin and I don't see an option for removing an index unless there is a name attached to the index. If I would write an sql statement to drop the index how would I write it if the index doesn't have a name. 

[Larry]

MySQL will name your indexes for you, even if you don't name them. I'm not sure how phpMyAdmin reflects this or not.

[luisfalcon]

check this command.

 

SELECT * FROM `information_schema`.`INNODB_SYS_INDEXES` WHERE table_id = (select table_id from `information_schema`.`INNODB_SYS_TABLES` where name = 'database_name/table_name')

 

 

this will give you the name of all the indexes created for that table. named or unnamed. created by you or the system.

 

you could use this to build another level of subquery to automatically select the right index and drop it.

 

you could also run it like this

 

SELECT * FROM `information_schema`.`INNODB_SYS_INDEXES` WHERE table_id = (select table_id from `information_schema`.`INNODB_SYS_TABLES` where name like '%table_name')

 

However, if more than one database have tables with same names, it will return indexes for all those tables. so the first syntax is more specific. 

 

Hope this helps though late. =)

[Larry]

Thanks for sharing that!