I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with the following commands

ALTER TABLE outputs add spent BOOL;

that query took 6 days and 9 hours to complete. There was no index added, because it is a column with only two possible values. Does anyone have clues as to why this relatively simple query would take so long? FYI The outputs table has a primary key column and two other columns which are indexed. It has a total of 9 columns. I can edit this post if more information about my mysql configuration is required, but I'd need to know which parameters are important.

  • 1
    Two occurrences could cause such a delay - being blocked (probably repeatedly, up to RowCount times) by simultaneous table updates, or having to rebalance indices RowCount times. Did you drop all indices on the table before making the addition, and rebuild them after? Jul 27 '14 at 17:27
  • No I didn't do that because that seemed like extra work to destroy the index and then remake it again, since remaking the index would (I thought) involve the same raw data moving computation that adding the column would take. Is deleting the indexes and remaking them a standard procedure for adding columns?
    – almel
    Jul 27 '14 at 17:34
  • 3
    Dropping and rebuilding the indices is a standard procedure (to be considered) for any and all operations that affect a table as a whole: not only for adding a column but for bulk-inserts and large-scale updates. Deciding exactly when to do it is a matter of judgement, experience, and testing. Consider yourself tested in this instance. Jul 27 '14 at 17:41

Adding a column in MySQL 5.6 requires the full table to be rebuilt. This becomes online in MySQL 8.0 though, provided the column is at the end of the table! The MySQL manual covers this under 14.13.1 Online DDL Operations (search in page for "Adding a column"):

  • Rebuilds Table?: Yes

Adding secondary indexes (i.e. not the primary key) should always be delayed until the last step so they can be sorted and optimally created. In the manual page I linked to, any operation that says In Place=No it will make sense to drop indexes first as well.


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.