(Referring only to InnoDB tables.)
ADD COLUMN rebuilds ever record to make room for the extra column. (MySQL 8.0 has a faster way.)
ADD INDEX can do the work on the side; then plop the index in place. It can run through the table, collecting the columns for the new index, plus the columns of the PK, sort that, build a BTree of the results. The only tricky part is keeping track of changes while all that is going on. (The PK columns are how a secondary index can link to the actual rows.)
Doing almost anything involving the
PRIMARY KEY (
ADD, etc) is very costly because the ordering of the data as it is stored on disk is controlled by the PK. (Simply using the PK values is not costly.)
UNIQUE index has the added complexity of verifying uniqueness. (I am unclear on how much extra overhead this entails.)
ALTER is finished, the changes are permanent and will be maintained. For example, one never needs to "rebuild an index". (OK, there is some kind of exception for
Note about the version: 10.1 is about equal to MySQL 5.6. Several indexing performance changes have happened in 5.6 and 5.7. So far, 8.0 has on notable improvement (instant add column). In 5.5 and before, virtually every
ALTER was simply done with a full rebuild of the table.
Note about history: MySQL (hence MariaDB) was created with the idea of doing a reasonably good job as a RDBMS, with enough features to do most tasks. Over the years, certain pain points arose, and eventually have been, are, or will be addressed. Speed of
ALTER TABLE is the one you have hit; it has been only partially addressed by the version you are using.