0

Is there difference in disk space usage and performance between table (InnoDB) created "at once" with CREATE TABLE or result from multiple structure changes ADD/DROP COLUMN/INDEX.

4
  • There is a difference (and it is unpredictable) until ALTER TABLE with ALGORITHM=COPY is running. No difference when only ALGORITHM=INSTANT is used.
    – Akina
    Apr 2 at 8:53
  • @Akina The question is not for the instant or delayed ALTER TABLE. The tables can be altered month ago without concurrent operations and even the server can be reloaded. The question is for the layout of data after ALTER TABLE compared with the same table structure created with single CREATE TABLE.
    – i486
    Apr 2 at 11:26
  • 1
    ?? I have answered this. This depends on the altering operations and used algorithms. So in general - unpredictable.
    – Akina
    Apr 2 at 11:30
  • Available algorithms list depends on the table engine (only, it seems). And does not depend on the DBMS name.
    – Akina
    Apr 2 at 11:43

1 Answer 1

1

I am assuming the tables are InnoDB, because InnoDB is the default storage engine, and you didn't say you were using any different storage engine.

The tablespace is stored similarly whether it's a new table or an altered table.

CREATE TABLE does not take a lot of disk space. A new table is created as an empty tablespace, with a size of 128KB. As you fill the table with data subsequently, it will fill pages and expand the tablespace as needed. The file is expanded in increments of 4MB.

If you delete data, pages may be left fragmented (partially filled), or completely empty. The tablespace does not shrink, even if you empty pages.

ALTER TABLE does a very similar action when doing an alteration that requires a table copy. It creates a new tablespace file, and then fills it with data copied from the original table. The layout of pages may be different, since it may be filling the rows in a different order than they were inserted to the original table.

If the original table had empty pages, there is no need to allocate corresponding pages in the copy table. This is a way to eliminate partially-filled or unfilled pages. The altered table may be smaller on disk than the original table, as it has been "defragmented."

Unfortunately, there is no way to measure how much space you might recover during this process. InnoDB does not report how much space is "wasted" in partially-filled pages. The measure it reports is in the table status, it reports Data_free which includes only empty extents, which are sets of 64 consecutive pages.

I have even seen a tablespace grow slightly larger after an ALTER TABLE, because InnoDB defaults to filling each page to 15/16 full. If the original table had been more fully packed than that, an ALTER TABLE may result in an expansion. Again, there's no way to know in advance if this will happen. You just have to try it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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