grahamgr3 Posted April 25, 2014 Share Posted April 25, 2014 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. Link to comment Share on other sites More sharing options...
Larry Posted April 25, 2014 Share Posted April 25, 2014 MySQL will name your indexes for you, even if you don't name them. I'm not sure how phpMyAdmin reflects this or not. Link to comment Share on other sites More sharing options...
luisfalcon Posted February 27, 2015 Share Posted February 27, 2015 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. =) 1 Link to comment Share on other sites More sharing options...
Larry Posted February 28, 2015 Share Posted February 28, 2015 Thanks for sharing that! Link to comment Share on other sites More sharing options...
Recommended Posts