Contents
Is ALTER TABLE slow?
If using 5.1+, and the InnoDB plugin is enabled, adding/removing indices will be online as well. Not sure about renames. If using older version, ALTER TABLE is still the fastest—but will probably be horribly slow because all of your data will be re-inserted to a temporary table under the hood.
What does ALTER TABLE statement do?
The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.
How can we alter the table?
ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type; Alters the table by changing the datatype of column. ALTER TABLE table_name RENAME TO new_table_name; Changes the name of a table in the currently connected to database.
Why does alter table take a long time?
Alter table takes a long time with a big data like in your case, so avoid to use it in such situations, and use some code like this one: DB2 z/OS does a virtual add of the column instantly. And puts the table into Advisory-Reorg status.
How long does MySQL alter table add column take?
I am not sure what to do. 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.
When to use alter table add column statement?
Then ALTER TABLE ADD COLUMN statements take substantial time to add new columns at the end of the table. This example does the same preparation of a large table as the previous example. However, you can’t simply enable lab mode within an interactive SQL session.
What are the parts of the ALTER TABLE statement?
The ALTER TABLE statement has these parts: The name of the table to be altered. The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table. The data type of field. The field size in characters (Text and Binary fields only).