Check

There are multiple ways to retrieve the same index information...

CREATE

ALTER TABLE mytable ADD INDEX(mycolumn);

or...

CREATE INDEX myindex ON mytable(mycolumn);

Index creation will be online where possible but may resort to a blocking (copy and switch) approach if necessary. 

To force an online rebuild (and fail with an error if it's not possible)...

CREATE INDEX myindex ON mytable(mycolumn) ALGORITHM = INPLACE;

Invisible Column Keys

If you cannot change the table structure to include an extra visible column to use as a PRIMARY KEY (perhaps it's a third party application), you can make use of the INVISIBLE COLUMN feature.

ALTER TABLE mytable

  ADD COLUMN _mypk BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE,

  ADD PRIMARY KEY (_mypk);

An alternate approach...

ALTER TABLE mytable

  ADD COLUMN _mypk BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())) INVISIBLE,

  ADD PRIMARY KEY (_mypk);

DROP

DROP INDEX myindex ON mytable;

Unused Indexes

To enable statistics collection...

SET GLOBAL userstat=1;

SELECT * 

  FROM INFORMATION_SCHEMA.TABLE_STATISTICS;

SELECT * 

  FROM INDEX_STATISTICS;

Report Unused Indexes...

SELECT DISTINCT s.TABLE_SCHEMA,

       s.TABLE_NAME,

       s.INDEX_NAME

  FROM information_schema.statistics `s` 

  LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA

                                                     AND  s.TABLE_NAME=INDXS.TABLE_NAME

                                                     AND  s.INDEX_NAME=INDXS.INDEX_NAME)

 WHERE INDXS.TABLE_SCHEMA IS NULL;

Defragmentation

Bibliography