Estimating Alter Table Progress

Unknown
Unknown
in Dev & Ops

If you'e like me and get antsy when a long running alter table statement is running then there is a fairly easy way to estimate your progress by looking at the temporary tables.

The SQL

First you need to figure out the number of rows in the table you are altering. Do this using the rows column of the table status statement.

show table status like '<table name>';

Next you can plug this in to the temporary table.

select 100 * table_rows / <rows>
from information_schema.global_temporary_tables;

How it works

Most table alters that are going to take a long time do so by creating a temporary table and copying all rows to it. So comparing the row count of the temporary table to that of the original table will give you a basic idea of how far along the alter is.

Categories