Question 245804 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Does an ALTER TABLE take longer for a larger table?

If I have a table A with 1000 rows, and a structurally identical table B but this time with 1000000 rows, will an ALTER TABLE (for example to add two new columns) take longer for table B? If so will it take proportionally longer depending on how many more rows there are?

Further to this, what if the ALTER TABLE statement doesn’t just add new columns, what if it changes the structure of existing columns?

This basically comes down to how MySQL stores data at a low level which is not something I am familiar with.

Tags:

2

Answers


  1. 0

    Yes it takes longer:

    As it needs to check every row, if the altered column accept the content of the row, so a bigger table in number of rows, will take longer to process.

    a look into the manual will give you more detail about Alter

    Login or Signup to reply.
  2. 0

    Appending columns can be instant when appended, or in 10.4+ if alter_algorithm='INSTANT' it can be added anywhere, with a different from normal storage (with storage consequences).

    Other operations, some are possible, documentation is extensive. You can test with:

    SET STATEMENT alter_algorithm='INSTANT' FOR ALTER TABLE tab MODIFY COLUMN c int;

    And if it cannot be done instantly it will error.

    ref: MariaDB KB InnoDB online

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search