Optimize mySql to quickly add a column adding table

Question

I have a table containing 170,002,225 rows with approximately 35 columns and two indexes. I want to add a column. The alter table team took about 10 hours. None of the processors seemed busy during this time and did not expect excessive waiting for I / O. This is on a 4 high-performance box with tons of memory.

Is that the best I can do? Is there something I can consider to optimize the add column when setting up db?

Answer 1

In the past, I came across a very similar situation, and I improve the performance of the operation in this way:

• Create a new table (using the structure of the current table) with the new column included.
• execute INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;
• rename current table
• rename the new table using the name of the current table.

Answer 2

ALTER TABLE in MySQL is actually going to create a new table with a new schema, and then return all the data and delete the old table. You can save some time by creating a new table, loading data and renaming the table.

From the MySQL High Performance Books (Percona guys):

The usual trick to efficiently load the MyISAM table is to disable the keys, load the data and change the keys:

 mysql> ALTER TABLE test.load_data DISABLE KEYS; -- load data mysql> ALTER TABLE test.load_data ENABLE KEYS; 

Answer 3

Well, I would recommend using the latest Percona MySQL builds plus, as there is the following note in the MySQL manual

In other cases, MySQL creates a temporary table, even if the data strictly does not need to be copied. For MyISAM tables, you can speed up the operation of re-creating the index (which is the slowest part of the process change) by setting the System myisam_sort_buffer_size variable to a high value.

First you can do ALTER TABLE DISABLE KEYS , then add a column, and then ALTER TABLE ENABLE KEYS . I don’t see anything here that can be done.

By the way, you can not go MongoDB? It does not restore anything when you add a column.

Answer 4

Maybe you can delete the index before changing the table, because most of the time it takes to build the index?