mysqlsql

Drop Column from Large Table

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?

Solution

  • 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 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.