ALTER TABLE in MySQL: Friend or Foe?

The ALTER TABLE statement is one of the most frequently used statements in the MySQL world – the statement allows you to add, delete or modify columns in a table. In this blog post we will try to look deeper into what it is, what it does and when should it be used.

What is ALTER TABLE and What Does it Do?

As already mentioned above, the ALTER TABLE statement enables DBAs and developers to add, delete or modify columns in a table. Simply put ALTER TABLE changes the structure of a table – it enables you to add, delete columns, add or remove indexes, rename columns or change their type.

When and How Do I Use ALTER TABLE?

In order to use ALTER TABLE you generally need the ALTER, CREATE and INSERT privileges. For renaming a table, required privileges are ALTER and DROP for the old table, then CREATE, ALTER and INSERT privileges for the new table to be created. To assign the required privileges to a certain user, you can use the following query:

GRANT ALTER, CREATE, INSERT ON database.* TO 'demo_user';

Replace database with your database name, the wildcard with the table name if you wish the privileges to only be applicable to certain tables (the wildcard makes the privilege applicable across all tables) and demo_user with the name of your user. If you want the privileges to be used across all databases and all tables within them, simply replace database with a wildcard:

GRANT ALTER, CREATE, INSERT ON *.* TO 'demo_user';

In order to actually make use of the ALTER TABLE statement, run a query that changes the structure of a table – ALTER TABLE is used to add, delete or modify columns in a table: the query can also be used to add indexes to columns. Here’s a few basic examples of most frequently used queries:

ALTER TABLE demo_table ADD column_name VARCHAR(255) NOT NULL DEFAULT ‘’; T

his query would add a column column_name to a table demo_table. Add FIRST to the end of the query to make the column the first column in the table.

ALTER TABLE demo_table ADD column_2 VARCHAR(255) NOT NULL DEFAULT ‘’ AFTER column_1; T

his query would add a column column_2 after the column column_1 on a table demo_table.

ALTER TABLE demo_table ADD COLUMN column_2 INT GENERATED ALWAYS AS (column_1 + 1) STORED; 

This query would add a generated column to the table.

ALTER TABLE demo_table DROP COLUMN demo_column; 

This query would drop the column demo_column on a table demo_table.

ALTER TABLE demo_table ADD INDEX demo_index(demo_column); 

This query would add an index named demo_index (names can be chosen) on a column called demo_column in a table called demo_table.

ALTER TABLE demo_table ADD INDEX (demo_column), ADD UNIQUE (demo_unique); 

This query would add an index on a column demo_column and an unique index on the demo_unique column.

ALTER TABLE demo_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4; 

This query would change the default character set of a specific column.

ALTER TABLE demo_table CONVERT TO CHARACTER SET charset_name; 

This query would change the default character set of the table and all character (CHAR, VARCHAR and TEXT) columns.

ALTER TABLE demo_table PARTITION BY HASH(demo_column) PARTITIONS 8; 

This query would partition the column demo_column into 8 partitions by hash.

ALTER TABLE demo_table TABLESPACE tablespace_1 STORAGE DISK; 

This query would convert the table demo_table to disk-based storage.

If you are adding indexes, keep in mind that you can add different types of indexes (for example, a BTREE index or a FULLTEXT index), you can also add an index that covers only a certain amount of characters in a column with a query like so:

ALTER TABLE demo_table ADD INDEX demo_index(column_name(10));

The above query would add an index called demo_index on the first 10 characters of the column called column_name in a table called demo_table.

Indexes in MySQL are a complex beast and they really deserve a topic of their own so we will not go into details here, but if you want to learn more, our earlier post about MySQL indexes should provide some more insight.

How Does ALTER TABLE Work?

ALTER TABLE in MySQL has its own subtleties. As of the most current version of MySQL, i.e. MySQL 8.0. There are 3 algorithms for which affects how the ALTER TABLE performs for such alterations. These are:

• COPY

  • Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. In most cases, this algorithm can be very expensive in terms of resource usage especially for big and large tables. When this algorithm is chosen or selected, all concurrent DML is not permitted so therefore any subsequent queries referencing to the affected table will have to wait or queued into the process list. Chances are, you’ll get your database stuck if connections are maxed out.

• INPLACE

  • Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

• INSTANT

  • Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

MySQL’s ALTER TABLE process might not be an issue with smaller tables, but if your data set is bigger you can run into issues – many people have experienced ALTER TABLE queries that have taken hours, days or even weeks to complete. In most cases, that happens because of MySQL’s table alteration process outlined above. However, there is a way to at least slightly reduce the time the query takes to complete:

1. Create a new table like your source table with your desired structure by running

   CREATE TABLE demo_table_new LIKE demo_table;

    then adjusting its structure. In this case the demo_table is the source table and demo_table_new is the new table.

2. Insert data into the new table.
3. Rename the old table to demo_table_old (adjust the name according to your needs).
4. Rename the new table to the former name of the old table.
5. Finally, copy the rows from the old table to the new table and, if needed, create indexes.

Although the steps above work fine. Yet, in real-world case scenarios, DBA’s or developers take the lean to use Percona’s pt-online-schema-change or using Github’s gh-ost. You can take a look at our previous post Top Open Source Tools for MySQL & MariaDB Migrations which takes an overview of these schema-change tools.

Anyway, what we have described above is frequently known as the “shadow copy” approach: in essence, you build a new table with the desired structure, then perform a rename and drop to swap the two tables. There also is another way: you can also swap servers around and run ALTER TABLE on servers that are not in production. For MyISAM, you can DISABLE KEYS, load data, then ENABLE KEYS.

ALTER TABLE Gotchas

If you are using the ALTER TABLE statement to create indexes (you can also use the CREATE INDEX statement), it is advised to create indexes after inserting the data because that’s a pretty well known way of speeding up processing not only in MySQL, but also in other database management systems, such as Oracle. In general though, keep in mind that most ALTER TABLE operations should be expected to cause some issues (interruption of service) to MySQL.

There also is another way to speed up the entire process though, albeit it’s a little bit more advanced: if you can convince MySQL to only modify the table’s .frm file (.frm files describe the definition of the table) and leave the table alone, the process will be faster:

1. Create an empty table with the same layout as the old table without modifying it.
2. Close all tables in use and prevent all new tables from being opened by running 

   FLUSH TABLES WITH READ LOCK.

3. Swap the .frm files.
4. Release the read lock by running UNLOCK TABLES.

Also keep in mind that if you want to modify a column and the syntax seems correct but you still get an error, it might be time to look into a different syntax. For example:

ALTER TABLE demo_table ADD long VARCHAR(255); 

A query like this would error out because long is a reserved word. In order to avoid such an error, escape the word with backticks:

ALTER TABLE demo_table ADD `long` VARCHAR(255);

It is also worth noting that the column names can only be escaped using backticks and not with single quotes or double quotes. For example, a query like so would error out too:

ALTER TABLE demo_table CHANGE COLUMN ‘demo_column’ ‘demo_column_2’ VARCHAR(255);

Summary

MySQL uses the ALTER TABLE statement to add, delete or modify columns in a table. In order for the statement to be successfully executed, you must have the ALTER,CREATE and INSERT privileges for the table. The statement also has a few subtleties unique to itself: its performance can suffer when running on very large tables due to the way it works, but as long as you know how the statement works and what it does you should be just fine.