Alter Table

These are native MySQL Alter Table recommendations for 8.0 and above production systems. If they don't fit your needs, check out Percona Tools as well.

Add Index

This alter command allows adding an index to an active table in production.

It won't block during the update, however it can block at the very start and end when attempting to get a table metadata lock if other long-running transactions are active. If this happens you can either wait or kill those longer-running transactions so that this command can complete.

ALTER TABLE table_name
  ALGORITHM=INPLACE,
  LOCK=NONE,
  ADD INDEX some_index_name (field,list,of,index,cols)