# 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](https://www.wswapps.com/books/mysql/page/percona-tools "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` - Does not require any locking. This is always the best option but only supported in a few scenarios.
- `ALGORITHM=COPY` - Blocks the table during rebuild. Consider using [pt-online-schema-change](https://www.wswapps.com/books/mysql/page/percona-tools "Percona Tools") if you don't have (many) foreign keys. Cannot use LOCK=NONE with this algorithm.

## Add Index

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

Uses `ALGORITHM=INPLACE`

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

## Modify Column - Non FK

This alter command allows modifying a column type in production.

Generally you can only use INPLACE for things like slightly extending a varchar type, or other scenarios where the column width/type doesn't change. You can always try INPLACE without fear and you will get an error if it's not allowed.

<p class="callout warning">Warning: This option won't work for columns that are part of a foreign key.</p>

Uses `ALGORITHM=INPLACE` or `COPY`

```
ALTER TABLE my_table
  ALGORITHM=INPLACE,
  LOCK=NONE,
  MODIFY COLUMN user_name VARCHAR(45) NOT NULL
```

<p class="callout danger">Some types of updates that extend the data length may trigger the following error. In this case you can either use COPY which will block the table, or use [pt-online-schema-change](https://www.wswapps.com/books/mysql/page/percona-tools "Percona Tools").  
  
SQL Error \[1846\] \[0A000\]: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.</p>