MySQL
SQL

MySQL ALTER TABLE Explained

intro

ALTER TABLE queries are some of the most frequent friends for those who modify data within database management systems – read this blog and learn how they work internally.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

MySQL ALTER TABLE queries help us modify – or alter – data in our databases. These kinds of SQL queries are amongst some of the most frequently used queries that help us add, delete, or modify data within the tables in MySQL Server and its counterparts, and the same SQL statement is also used to add, modify, or drop SQL indexes, too.

The Basics

The ALTER TABLE statement looks like so:

ALTER TABLE demo ADD COLUMN demo_column VARCHAR(17) AFTER username;

The ALTER TABLE MySQL statement in DbVisualizer
The ALTER TABLE MySQL statement in DbVisualizer

The structure of this SQL query is rather simple – first, the table name, then the action with any additional actions (the add column statement above also modifies the length of the column before adding it on the table.) For example, if we’d like to add a fulltext index on a table called demo, we would issue a MySQL ALTER TABLE statement like so:

ALTER TABLE demo ADD FULLTEXT INDEX demo_ft(demo_column);

Adding a SQL fulltext index in DbVisualizer
Adding a SQL fulltext index in DbVisualizer

Such an ALTER TABLE MySQL statement is rather simple, right? This simple MySQL ALTER TABLE statement has a lot of use cases – care to go through them with us?

The Use Cases of ALTER TABLE

The ALTER TABLE statement can be used to:

  • Add, modify, or drop columns belonging to a certain table.
  • Add, modify, or delete indexes from a table.
  • Add, drop, discard, import, truncate, reorganize, repair, remove, or otherwise modify partitions.
  • Specify options within a table (one can specify the size of AUTO_INCREMENT, specify the average row length, the default character set, default collations, set comments, set directories that hold indexes or data, etc.)
  • Change storage engines of tables.
  • Change the row format of rows in a table.
  • Change data types (e.g. change the VARCHAR data type to INT on a certain column, etc.)
  • Rename columns, add constraints, and do a whole bunch of other things.

By now, you should get it – the ALTER TABLE MySQL statement can perform pretty much any action related to modifying data within a table.

How Does ALTER TABLE Work?

The way MySQL ALTER TABLE statement works is a little different to SELECT, INSERT, UPDATE, or DELETE queries that you are so used to – once the statement is used, your database management system will go through a couple of phases (for convenience, the original table that you run queries on will be called A, and the other will be called B):

  1. MySQL Server RDBMS will take a copy of the data within the table A.
  2. MySQL Server will create a table B that is exactly the same as the table A.
  3. MySQL Server will insert all of the data within the table A into the table B.
  4. MySQL Server will perform all modifying operations within the table B.
  5. MySQL Server will switch the table A with the table B.

In most cases, this process will take milliseconds and you won’t even notice it as you go along – yet, in some cases, this process can also take hours or even weeks to complete. Everything depends on your database configuration – all database management systems make use of parameters defined within a file that they’re dependent upon when completing such operations:

  • In MySQL Server, MariaDB Server and Percona Server, this file is my.cnf and can be found in a variety of locations, most likely within the /var/lib/mysql folder.
  • In PostgreSQL and related database management systems (TimescaleDB and the like), the file is called postgresql.conf.
  • In SQL Server, the file is called ConfigurationFile.ini.

To optimize the performance of ALTER TABLE MySQL, optimize the setting that deals with the inner working of data within your database instance: in MySQL Server, that’s innodb-buffer-pool-size. Setting the buffer pool size to 60-80% of the RAM available within your system is a good idea – for those who wonder, the buffer pool size and related settings in other database management systems refer to the amount of operating memory that can be used for mission-critical SQL queries that modify data – such queries include ALTER TABLE MySQL as well as other SQL queries.

Optimizing ALTER TABLE Further

Setting the innodb-buffer-pool-size parameter to an optimal size will be a good starting point, however, there are also a couple of things that you need to keep in mind as well:

  • Different database management systems put a different “weight” on this query in terms of performance (i.e. for some, optimizing the parameters won’t do as much good as for others.)
  • Different database management systems have certain limitations as to where the modifications done towards the ALTER TABLE MySQL query apply to: in many cases, database management systems only have one or two storage engines that support modifications relevant to this SQL query (in MySQL Server, that’s InnoDB and Percona XtraDB, for other database management systems the results may differ.)

The modifications done to impact the performance of the ALTER TABLE MySQL statement very often have an impact on all other types of queries due to the internal workings of the database management system (coming back to the buffer pool, the bigger it is, the more data it can cache.) The impact is almost always positive – just make sure to not overload your server when playing around with the settings.

Having this in mind, keep in mind that for the ALTER TABLE MySQL query to work well, you need to have a decent amount of storage space as well – if there’s not enough storage space on the disk, your database management system will present an error. The inner workings of MySQL ALTER TABLE will usually be quick unless you’re dealing with hundreds of millions of rows and above – in that case, you may see no results until the SQL query has finished executing (many database management systems come with storage engines that support the ACID functionality which is one of the primary reasons of you seeing no results in this case.)

Also, do note that not all operations that alter the data within the table have to necessarily work with the data itself – renaming columns, working with partitions, changing the row format or the storage engine will usually be blazing quick operations regardless of how many rows your table has because the query simply won’t touch them and work on the surface level.

Optimizing Databases

After you’ve optimized your ALTER TABLE queries, it’s time to scratch past the surface level for your database management system as well. That can be accomplished by using proven SQL clients like DbVisualizer – its powerful features will help you work with everything ranging from query maintenance to visualizing your queries in real-time – you will even be able to see how certain tables in your database infrastructure look like when they’re drawn out if you head over to the References section:

*Observing the MySQL Server table structure in DbVisualizer*
Observing the MySQL Server table structure in DbVisualizer

You will be able to observe information relevant to the data within your tables, columns that you’ve created, DbVisualizer will show you the row count within the table, and provide you information about indexes you’ve built as well:

*Information about the SQL indexes in DbVisualizer*
Information about the SQL indexes in DbVisualizer

If you wish, you will also be able to copy over the DDL to make a copy of the table and the data within it – just head over to the DDL section and copy everything over there:

*DDL code in DbVisualizer*
DDL code in DbVisualizer

DbVisualizer also comes with other features unique to itself. Some of them allow you to improve the security of your work within the database management systems as well – by setting permissions, you will be able to allow or deny SQL code to be executed as well:

*Setting the permissions for DbVisualizer’s SQL commander*
Setting the permissions for DbVisualizer's SQL commander

Did we tell you that you can evaluate DbVisualizer for free and join the realms of NASA, Volkswagen, and other companies using the tool? Give it a try today!

Conclusion

In this blog, we’ve walked you through one of the most important queries when modifying data within your database infrastructure – the ALTER TABLE MySQL query. You’ve learned what it is, how it works internally, and how it can help you achieve your goals within the database space.

Follow our blog for more updates, and we’ll see you in the next blog!

Frequently Asked Questions

What is the MySQL ALTER TABLE Statement?

The MySQL ALTER TABLE statement is a SQL query helping us modify - or ALTER - the data within our MySQL server instances. This statement works with MySQL Server, MariaDB Server, and Percona Server.

How Can the MySQL ALTER TABLE Statement Help Me?

The MySQL ALTER TABLE statement can help you to perform operations on columns, indexes, partitions, and help you orient yourself within the realm of table options.

How Does the MySQL ALTER TABLE Query Work?

The MySQL ALTER TABLE SQL query works by making a copy on the table on the disk, then performing all of the necessary operations on that table, and swapping the two tables afterwards. That’s why you should make sure to have enough disk space for operations to complete.

Why Should I Use a SQL Client?

You should use a SQL client like the one provided by DbVisualizer because it supports many database technologies, is capable of providing you with ways to access, explore, and optimize your database instances in ways you never thought were possible, and is the leading choice for world’s best data experts.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13
title

SQL Server DATEADD: The Complete Guide

author Ochuko Onojakpor tags SQL SQL SERVER 7 min 2024-06-10

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗