Large tables and ENUM
Continuation of the article Large tables and unique keys.
I again needed to change the structure of a fairly large table in MySQL. The changes concerned the field of type ENUM.
so
It is given. MyISAM table
In the table there is a field of type ENUM.
Size - 145 million entries.
The disk capacity is 12 GB.
The table also has a unique key on the VARCHAR string field (150).
It is necessary to change the list of ENUM field constants.
That is, we have the field `my_field` ENUM ('A', 'B') NOT NULL. It is necessary to change to ENUM ('C', 'D', 'E', 'F') NOT NULL.
')
It makes no sense to make 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 unreasonable amount of time.
However, I remembered in my memory that I had already seen something similar. I finally found the recipe I needed in the book “High Performance MySQL” , where by the way I found the hack from my previous article.
The recipe is simple - the substitution of the file, where the definition of the structure of the table .FRM is stored .
1. Create a new table with the structure of 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. Lock the tables: FLUSH TABLES WITH READ LOCK
4. in the directory with the required database, copy the file test_struct.frm over test.frm.
5. Remove the lock: UNLOCK TABLES
All is ready. You can check.
What should be remembered? In MySQL, the data in fields of type ENUM are integers. Countdown starts from one.
Thus it was: the constant A = 1, B = 2.
It became: C = 1, D = 2, E = 3, F = 4. - As a result, all records with the value “A” after our changes began to appear as “C”, and all “B” as “D”, because their internal representation remains the same - 1 and 2, respectively.
Where else can this recipe apply?
1. Remove the AUTO_INCREMENT attribute from the field.
2. Add, change or remove constants in the fields of type SET .
I again needed to change the structure of a fairly large table in MySQL. The changes concerned the field of type ENUM.
so
It is given. MyISAM table
In the table there is a field of type ENUM.
Size - 145 million entries.
The disk capacity is 12 GB.
The table also has a unique key on the VARCHAR string field (150).
It is necessary to change the list of ENUM field constants.
That is, we have the field `my_field` ENUM ('A', 'B') NOT NULL. It is necessary to change to ENUM ('C', 'D', 'E', 'F') NOT NULL.
')
It makes no sense to make 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 unreasonable amount of time.
However, I remembered in my memory that I had already seen something similar. I finally found the recipe I needed in the book “High Performance MySQL” , where by the way I found the hack from my previous article.
The recipe is simple - the substitution of the file, where the definition of the structure of the table .FRM is stored .
1. Create a new table with the structure of 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. Lock the tables: FLUSH TABLES WITH READ LOCK
4. in the directory with the required database, copy the file test_struct.frm over test.frm.
5. Remove the lock: UNLOCK TABLES
All is ready. You can check.
What should be remembered? In MySQL, the data in fields of type ENUM are integers. Countdown starts from one.
Thus it was: the constant A = 1, B = 2.
It became: C = 1, D = 2, E = 3, F = 4. - As a result, all records with the value “A” after our changes began to appear as “C”, and all “B” as “D”, because their internal representation remains the same - 1 and 2, respectively.
Where else can this recipe apply?
1. Remove the AUTO_INCREMENT attribute from the field.
2. Add, change or remove constants in the fields of type SET .
Source: https://habr.com/ru/post/76356/
All Articles