Dropping or Changing an Index

Sometimes, indexes outlive their usefulness, and they need to be changed or dropped (removed). When making any change to an index, you'll need to drop the index first and then rebuild it with the new definition.

To drop a primary key, use this syntax:

ALTER TABLE tablename DROP PRIMARY KEY;

To drop an ordinary, unique, or full-text index, you need to specify the index name, like this:

ALTER TABLE tablename DROP INDEX indexname;

or like this:

DROP INDEX indexname ON tablename;

If you're not sure what the index name is, SHOW KEYS will reveal all:

SHOW KEYS FROM tablename;