ALTER TABLE ADD COLUMN takes a long time

by

Your ALTER TABLE statement implies mysql will have to re-write every single row of the table including the new column. Since you have more than 2 million rows, I would definitely expect it takes a significant amount of time, during which your server will likely be mostly IO-bound. You’d usually find it’s more performant to do the following:

CREATE TABLE main_table_new LIKE main_table;
ALTER TABLE main_table_new ADD COLUMN location VARCHAR(256);
INSERT INTO main_table_new SELECT *, NULL FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;

This way you add the column on the empty table, and basically write the data in that new table that you are sure no-one else will be looking at without locking as much resources.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How can i optimize MySQL’s ORDER BY RAND() function?
  3. MYSQL OR vs IN performance
  4. Why does MYSQL higher LIMIT offset slow the query down?
  5. How to Perform an UPSERT so that I can use both new and old values in update part
  6. Is there a REAL performance difference between INT and VARCHAR primary keys?
  7. JDBC batch insert performance
  8. SQL Performance UNION vs OR
  9. How do I modify a MySQL column to allow NULL?
  10. Why is MySQL InnoDB insert so slow?
  11. MySQL stored procedures use them or not to use them
  12. UUID performance in MySQL?
  13. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  14. Optimal MySQL settings for queries that deliver large amounts of data?
  15. DOs and DONTs for Indexes [closed]
  16. How to move columns in a MySQL table?
  17. MySql views performance [closed]
  18. MySQL performance: multiple tables vs. index on single table and partitions
  19. “SELECT COUNT(*)” is slow, even with where clause
  20. Does Order of Fields of Multi-Column Index in MySQL Matter
  21. MySQL load data infile – acceleration?
  22. How to remove constraints from my MySQL table?
  23. is php sort better than mysql “order by”?
  24. How can “set timestamp” be a slow query?
  25. Would it help to add index to BIGINT column in MySQL?
  26. Why is connecting to MySQL server so slow?
  27. Mysql performance on 6 million row table
  28. Disadvantages of quoting integers in a Mysql query?
  29. Composite Primary Key performance drawback in MySQL
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment