November 25, 2009 at 15:08Large tables and ENUM
Continuation of the article Large tables and unique keys.
I again had the need to change the structure of a fairly large table in MySQL. The changes concerned a field of type ENUM.
So
given. MyISAM
table The table has a field of type ENUM.
Size - 145 million records.
The volume on the disk is 12 GB.
Also in the table there is a unique key on the string field VARCHAR (150).
You must change the list of constants of the ENUM field.
That is, we have the field `my_field` ENUM ('A', 'B') NOT NULL. It should be changed to ENUM ('C', 'D', 'E', 'F') NOT NULL.
It makes no sense to do ALTER TABLE CHANGE COLUMN, because on such data volumes and with a unique index, it will take forever. Cause? MySQL, when changing the structure of the table, in most cases creates a temporary table of the desired structure, where it copies all the data line by line. And creating a unique key line by line in such volumes takes an exorbitant amount of time.
However, it stuck in my memory that I had already met something like this somewhere. I eventually found the recipe I needed in the book “High Performance MySQL” , where by the way I also found the hack from my previous article.
The recipe is simple - replacing the file where the definition of the structure of the .FRM table is stored .
1. Create a new table with the same structure as the original: CREATE TABLE test_struct LIKE test.
2. Change the ENUM field in the new table: ALTER TABLE `test_struct` CHANGE` my_field` ENUM ('C', 'D', 'E', 'F') NOT NULL.
3. We lock the tables: FLUSH TABLES WITH READ LOCK
4. in the directory with the desired database, copy the file test_struct.frm over test.frm.
5. Unlock: UNLOCK TABLES
Everything is ready. You can check.
What should be remembered? In MySQL, data in fields of the ENUM type are integers. The countdown starts from one.
Thus it was: constant A = 1, B = 2.
Now: C = 1, D = 2, E = 3, F = 4. - As a result, all records with the value “A” after our changes began to display as “C” , and all “B” - as “D”, because their internal representation remained the same - 1 and 2, respectively.
1. Remove the AUTO_INCREMENT attribute from the field.
2. Add, change or delete constants in fields of type SET .
I again had the need to change the structure of a fairly large table in MySQL. The changes concerned a field of type ENUM.
So
given. MyISAM
table The table has a field of type ENUM.
Size - 145 million records.
The volume on the disk is 12 GB.
Also in the table there is a unique key on the string field VARCHAR (150).
You must change the list of constants of the ENUM field.
That is, we have the field `my_field` ENUM ('A', 'B') NOT NULL. It should be changed to ENUM ('C', 'D', 'E', 'F') NOT NULL.
It makes no sense to do ALTER TABLE CHANGE COLUMN, because on such data volumes and with a unique index, it will take forever. Cause? MySQL, when changing the structure of the table, in most cases creates a temporary table of the desired structure, where it copies all the data line by line. And creating a unique key line by line in such volumes takes an exorbitant amount of time.
However, it stuck in my memory that I had already met something like this somewhere. I eventually found the recipe I needed in the book “High Performance MySQL” , where by the way I also found the hack from my previous article.
The recipe is simple - replacing the file where the definition of the structure of the .FRM table is stored .
1. Create a new table with the same structure as the original: CREATE TABLE test_struct LIKE test.
2. Change the ENUM field in the new table: ALTER TABLE `test_struct` CHANGE` my_field` ENUM ('C', 'D', 'E', 'F') NOT NULL.
3. We lock the tables: FLUSH TABLES WITH READ LOCK
4. in the directory with the desired database, copy the file test_struct.frm over test.frm.
5. Unlock: UNLOCK TABLES
Everything is ready. You can check.
What should be remembered? In MySQL, data in fields of the ENUM type are integers. The countdown starts from one.
Thus it was: constant A = 1, B = 2.
Now: C = 1, D = 2, E = 3, F = 4. - As a result, all records with the value “A” after our changes began to display as “C” , and all “B” - as “D”, because their internal representation remained the same - 1 and 2, respectively.
1. Remove the AUTO_INCREMENT attribute from the field.
2. Add, change or delete constants in fields of type SET .