5

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.

Improve this question
3
  • 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?
    – Pieter Geerkens
    Commented Jul 27, 2014 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
    Commented Jul 27, 2014 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.
    – Pieter Geerkens
    Commented Jul 27, 2014 at 17:41

2 Answers 2

Reset to default
3

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.

Improve this answer
3
0

If you're using phpMyAdmin, I also found that executing queries via Console seems to be faster than executing them via the SQL tab :)

I'm not sure of this, but if it's true, perhaps the SQL tab is modifying the query in some way, making it slower?

Improve this answer
1
  • I am not sure how this answers the question.
    – Rohit Gupta
    Commented Dec 21, 2023 at 9:44

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.