Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
grahamgr3

Mysql Command For Dropping An Unnamed Index

Recommended Posts

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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. =)
  • Upvote 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...