Time taken to drop/add index is lesser than adding a column. Why is that?

Question

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though.

I'm performing an alter by default it does ONLINE alter.

When I add column it takes at least 50 mins for 50G table in our infrastructure, but when I performed removing and adding a composite index of 3 columns it took 8 mins.

Any idea internals of what it is doing?

Also does the duration changes if I'm adding an PK index/UK index.

Answer 1

(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 (DROP, 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.)

A UNIQUE index has the added complexity of verifying uniqueness. (I am unclear on how much extra overhead this entails.)

Once an 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 FULLTEXT.)

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.

Answer 2

Let me say in a simple way. When you create a new table/index and then you are inserting 10 rows so, index value will be 1 to 10. Now you are deleting 5 and 6 the row in the table so ,holes are formed. Next time while inserting rows from 11 to 20 then index 5 and 6th rows need to be refilled that's why it's taking time. If you drop a column and recreate index then value is auto increment that's why it's faster and there is no hole.