Why can MySQL 8 DDL be slow on a text-heavy table but not many records?

Question

I have a table with two simple columns and two mediumtext columns that looks like this:

create table page (
  id bigint auto_increment primary key,
  status tinyint not null,
  content mediumtext,
  screenshot mediumtext
) row_format=compressed;

The table stores a web page's entire source and an encoded screenshot with the former being as large as 7mb and the latter around 5mb (but the average value for both columns is about 500kb to 2mb).

The page table only has 50k records, which isn't much these days, but is about 20GB in size. When I tried to add a new simple column it took almost an hour:

alter table page add column comment varchar(255);

Meanwhile, when I add the same comment column to another table with 50k records and no text columns it happens within seconds.

Here's where I'm curious: I thought that text columns were more like pointers to the actual data, so adding a new column should not take long because we're not touching the text data. But given the long duration it's almost like we're restructuring the entire table, which is concerning because it'll make future DDL difficult. What might be happening in this case, and can I query for transactional, lock, or metadata to get more insight? I have innodb_file_per_table=on.

Another curiosity is I recall adding a new column to the same big table but it was a nearly instant operation. Assuming I remembered correctly, are there certain operations that restructure the entire table vs ones that do not?

Answer 1

I think the answer is that the whole row (or whole 'block'?) is "compressed". Hence, adding a column to a compressed table requires uncompressing, adding the column, then re-compressing.

When I have bulky text, I prefer to compress the text in the client, then store it into a MEDIUMBLOB. This cuts down on network traffic and server CPU effort. (Granted, the CPU is not an issue for you.)

It also may give better compression:

• InnoDB's compression gives about 2:1
• Virtually any text compressed with any of the popular compression tools gives about 3:1 compression.

Yes, the list of which ALTERs are faster or slow is very much a function of the layout of the data structures. For example, almost any change to the PRIMARY KEY requires a full rebuild (copy table over), but many non-Unique secondary index changes are "instant" (though some work may happen after the Alter returns).

Also, the Version matters. Certain ENUM changes were implemented long ago. The rest came in various stages with 5.6, 5.7, and 8.0. Occasionally someone comes up with another clever idea and yet another Alter becomes faster.

A general rule:

• If the Alter can be effected by changing just the table definition, it may be "instant".
• If the bytes on disk need to be changed or simply rearranged, it may involve copying over the entire table and/or rebuilding the index(es). (Of course, this requires you to know the internal implementation. Still, some of this can be easily guessed; or ask here for help.)