Skip to main content

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.

The advice below will always suggest the least-blocking option. In general:

    ALGORITHM=INPLACE - Requires a metadata table lock when the transaction starts and finishes. Otherwise will not block the table. If you have active long-running transactions you will need to kill them at the start and end of processing. ALGORITHM=INSTANT - 

    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)

     

    Back to top