MySQL: Drop Column from Large Table

Question

I have this largish table with three columns as such:

+-----+-----+----------+
| id1 | id2 | associd  |
+-----+-----+----------+
|   1 |  38 | 73157604 |
|   1 | 112 | 73157605 |
|   1 | 113 | 73157606 |
|   1 | 198 | 31936810 |
|   1 | 391 | 73157607 |
+-----+-----+----------+

This continues for 38m rows. The problem is I want to remove the 'associd' column but running ALTER TABLE table_name DROP COLUMN associd; simply takes too long. I wanted to do something like: ALTER TABLE table_name SET UNUSED associd; and ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250; then which apparently speeds up the process but it isn't possible in MySQL?

Is there an alternative to remove this column-- maybe creating a new table with only the two columns or getting a drop with checkpoints? Thanks!

Answer 1

The best solution in this case in MySQL is to:

1) change the table Engine to MyISAM

2) change whatever you want to do (Drop column, alter data type,etc..)

3) change it back to InnoDB

In this case the DBMS will not be locking/unlocking at each record iteration.

However note that this solution would be good if you have several things you want to change in your table/database, because once you revert it back to InnoDB, this will take the same amount of time to drop one column. So only consider this solution if you have multiple things to change in your database.

Answer 2

Anything that you do is going to require reading and writing 38m rows, so nothing is going to be real fast. Probably the fastest method is probably to put the data into a new table:

create table newTable as
    select id1, id2
    from oldTable;

Or, if you want to be sure that you preserve types and indexes:

create table newTable like oldTable;

alter table newTable drop column assocId;

insert into newTable(id1, id2)
    select id1, id2
    from oldTable;

However, it is usually faster to drop all index on a table before loading a bunch of data and then recreate the indexes afterwards.

Answer 3

Disclaimer: this answer is MySQL oriented and might not work for other databases.

I think in the accepted answer there are some things missing, I have tried to expose here a generic sequence I use to do this kind of operations in a production environment, not only for adding/removing columns but also to add indexes for example.

We call it the Indiana Jones' movement.

Create a new table

A new table using the old one as template:

create table my_table_new like my_table;

Remove the column in the new table

In the new table:

alter table my_table_new drop column column_to_delete;

Add the foreign keys to the new table

The are not generate automatically in the create table like command.

You can check the actual foreign keys:

mysql> show create table my_table;

Then apply them to the new table:

alter table my_table_new
  add constraint my_table_fk_1 foreign key (field_1) references other_table_1 (id),
  add constraint my_table_fk_2 foreign key (field_2) references other_table_2 (id)

Clone the table

Copy all fields but the one you want to delete.

I use a where sentence to be able to run this command many times if necessary.

As I suppose this is a production environment the my_table will have new records continuously so we have to keep synchronizing until we are capable to do the name changing.

Also I have added a limit because if the table is too big and the indexes are too heavy making a one-shot clone can shut down the performance of your database. Plus, if in the middle of the process you want to cancel the operation it will must to rollback all the already done insertions which means your database won't be recovered instantly (https://dba.stackexchange.com/questions/5654/internal-reason-for-killing-process-taking-up-long-time-in-mysql)

insert my_table_new select field_1, field_2, field_3 from my_table 
where id > ifnull((select max(id) from my_table_new), 0)
limit 100000; 

As I was doing this several times I created a procedure: https://gist.github.com/fguillen/5abe87f922912709cd8b8a8a44553fe7

Do the name changing

Be sure you run this commands inmediately after you have replicate the last records from your table. Idealy run all commands at once.

rename table my_table to my_table_3;
rename table my_table_new to my_table;

Delete the old table

Be sure you have a back up before you do this ;)

drop table my_table_3

Disclaimer: I am not sure want will happen with foreign keys that were pointing to the old table.