Table Maintenance

• Installing Percona Toolkit
• Altering tables
• Moving tables across databases
• Altering tables using an online schema change tool
• Archiving tables
• Cloning tables
• Partitioning tables
• Partition pruning and selection
• Partition management
• Partition information
• Efficiently managing time to live and soft delete rows

Introduction

One of the key aspects in maintaining a database is managing tables. Often, you need to alter a big table or clone a table. In this chapter, you will learn about managing big tables. Some open source third-party tools are used as MySQL does not support certain operations. The installation and usage of third-party tools are also covered in this chapter.

Altering tables

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

There are two algorithms for alter operations:

• In-place (default): Does not require copying whole table data
• Copy: Copies the data into a temporary disk file and renames it

If you want to add a new column to the employees table, you can execute the ADD COLUMN statement:

USE employees;
ALTER TABLE employees ADD COLUMN address varchar(100);

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

You can see that the number of rows affected is 0, which means that the table is not copied and the operation is done in-place.

If you want to increase the length of the varchar column, you can execute the MODIFY COLUMN statement:

ALTER TABLE employees MODIFY COLUMN address VARCHAR(255);

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you think that varchar(255) is not sufficient to store addresses, and you would like to change it to tinytext, you can use the MODIFY COLUMN statement. However, in this case, since you are modifying the data type of a column, all the rows of the existing table should be modified, which requires table copy, and DMLs are blocked:

ALTER TABLE employees MODIFY COLUMN address tinytext;

Query OK, 300024 rows affected (8.76 sec)
Records: 300024  Duplicates: 0  Warnings: 0

Adding a virtual generated column is just a metadata change and is almost instantaneous:

ALTER TABLE employees ADD COLUMN full_name VARCHAR(40) AS (CONCAT('first_name', ' ', 'last_name'));

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

However, adding a STORED GENERATED column and modifying the VIRTUAL GENERATED column is not on-line:

ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL;

Query OK, 300024 rows affected (7.13 sec)
Records: 300024  Duplicates: 0  Warnings: 0

Moving tables across databases

You can rename a table by executing the RENAME TABLE statement.

CREATE DATABASE prod;
CREATE TABLE prod.audit_log (id int NOT NULL, msg varchar(64));
CREATE DATABASE archive;

For example, if you want to rename the audit_log table audit_log_archive_2018, you can execute the following:

USE prod;
RENAME TABLE audit_log TO audit_log_archive_2018;

If you want to move the table from one database to an other, you can use dot notation to specify the database name. For example, if you want to move the audit_log table from the database named prod to the database named archive, execute the following:

USE prod;
SHOW TABLES;

+------------------------+
| Tables_in_prod         |
+------------------------+
| audit_log_archive_2018 |
+------------------------+
1 row in set (0.00 sec)

RENAME TABLE audit_log_archive_2018 TO archive.audit_log;
SHOW TABLES;

Empty set (0.00 sec)

USE archive;
SHOW TABLES;

+-------------------+
| Tables_in_archive |
+-------------------+
| audit_log         |
+-------------------+
1 row in set (0.00 sec)