0

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.

  • While adding column all table is re-writed. While adding index which will NOT became clustered re-write do not needed. – Akina Dec 10 '18 at 11:02
0

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.

  • The original question deals with why adding a new column takes longer than dropping/recreating an index. The additional time for adding a new column to a huge table entails potentially allocating additional storage to the database for each row of the large table. Dropping and recreating an index (if the index does not include the new column would not require any new space to be allocated. – Mark Stewart Dec 10 '18 at 15:46
  • There is no renumbering. There is no concept of "5th row", except by using ORDER BY ... LIMIT 5,1; it is not done when inserting/deleting. – Rick James Dec 10 '18 at 17:37
0

(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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.