Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Performance of MySQL ALTER TABLE ADD COLUMN AFTER COLUMN - on a large table

Tags:

mysql

I want to achieve the following use the following command to add a column to an existing table:

   ALTER TABLE foo ADD COLUMN bar AFTER COLUMN old_column; 

Can this option take substantially longer than the same command without the AFTER COLUMN option, as follows?

   ALTER TABLE foo ADD COLUMN bar; 

Will the first command use a greater amount of tmp table space during execution to perform the action?

Context: I have a very large table (think over a billion rows) and I want to add an additional column using the AFTER COLUMN option, but I don't want to be penalized too much.

like image 960
Drew Avatar asked Nov 18 '11 23:11

Drew


People also ask

Does number of columns affect performance in MySQL?

It could be negligible, but if you are near the boundary between being able to cache the entire table in memory or not, a few extra columns could make a big difference to the execution speed.

When you use ALTER TABLE to add a column the new column?

Syntax. The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows. ALTER TABLE table_name ADD column_name datatype; The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.

Does order of columns matter in MySQL?

Yes, column order does matter.

How do I add more columns to a table in MySQL?

The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ]; table_name.


2 Answers

Here's what I would do:

CREATE TABLE newtable LIKE oldtable; ALTER TABLE newtable ADD COLUMN columnname INT(10) UNSIGNED NOT NULL DEFAULT 0;  

I don't know the type of your column. I give an example with INT. Now here you can specify WHERE you want to add this new column. By default it will add it at the end unless you specify the AFTER keyword, if you provide it, you will have to specify in the order you will insert otherwise you need to put it at the end.

INSERT INTO newtable SELECT field1, field2, field3 /*etc...*/, newcolumn = 0 FROM oldtable;  

OR, if you added it between columns:

# eg: ALTER TABLE newtable ADD COLUMN columnname INT(10) UNSIGNED  NULL AFTER field2;  INSERT INTO newtable SELECT field1, field2, newcolumn = 0, field3 /*etc...*/ FROM oldtable;  

You can add a where clause if you want to do them in batch.

Once all the records are there

DROP TABLE oldtable; RENAME TABLE newtable to oldtable; 
like image 57
Book Of Zeus Avatar answered Sep 29 '22 06:09

Book Of Zeus


Create another table and alter the new table. ( like Book Of Zeus did )

And using ALTER TABLE newtable DISABLE KEYS and ALTER TABLE newtable ENABLE KEYS before and after the inserting query can make it faster. ( like below )

CREATE TABLE newtable ....; ALTER TABLE newtable ....;  ALTER TABLE newtable DISABLE KEYS; INSERT INTO newtable ....; ALTER TABLE newtable ENABLE KEYS;  DROP TABLE oldtable; 
like image 32
lqez Avatar answered Sep 29 '22 06:09

lqez