Add column + index in same statement

Question

Is there a way to add the following as one statement instead of two? Each of the following statements takes a few hours to run so I was wondering if on creation it can be indexed?

alter table main_table add `last_updated` datetime DEFAULT CURRENT_TIMESTAMP;
alter table main_table add index (last_updated);

As far as I know the column is added to the table (file) and the index is a separate file so even if they're merged in a single command they'd probably still take the same time — apokryfos, Apr 14 '18 at 01:06

score 3 · Answer 1 · answered Apr 14 '18 at 01:36

According to the manual, the relevant part of ALTER TABLE syntax is:

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]

alter_specification:
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...

which says that you can do both adding a column and an index in the same statement.

I have tested this (on MySQL 5.6) and it works fine:

alter table main_table add `last_updated` datetime DEFAULT CURRENT_TIMESTAMP,
add index (last_updated);

bishop · Accepted Answer · 2018-04-14T12:14:01.533

You didn't specify your MySQL server version or table engine, but if you're on InnoDB using a recent version of MySQL (> 5.6), have you considered using explicit Online DDL?

ALTER TABLE main_table
  ADD `last_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,
  ADD INDEX (`last_updated`),
      ALGORITHM=inplace,
      LOCK=none;

Note that there are some constraints on when the in-place algorithm can be used.

If you have foreign key constraints in effect, disable those or the copy algorithm will be used. And it'll be slow.

If your table was created pre-5.6, then you'll need to rebuild the table using a copy algorithm before you can switch to the inplace algorithm.

Finally, if you're using pre-5.6 or these don't work for whatever reason, consider using Percona's online-schema-change.

Add column + index in same statement

2 Answers2