I have a table in utf8
with > 80M records and one of the columns (char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) can contain just latin symbols ([a-zA-Z0-9]
). Does it have the sense to convert this column into latin1?
MySQL doc says:
To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible character length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.
I made a test - created 2 tables with the same 50M records:
CREATE TABLE `t_utf8` (
`c_1` char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
UNIQUE KEY `index_t_utf8_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
CREATE TABLE `t_lat` (
`c_1` char(6) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
UNIQUE KEY `index_t_lat_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
but MySQL says that they have almost the same size:
Name: t_lat
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 57557700
Avg_row_length: 30
Data_length: 1741668352
Max_data_length: 0
Index_length: 0
Data_free: 2097152
Auto_increment: NULL
Collation: utf8_general_ci
Create_options: row_format=COMPACT
Name: t_utf8
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 57554528
Avg_row_length: 31
Data_length: 1810874368
Max_data_length: 0
Index_length: 0
Data_free: 3145728
Auto_increment: NULL
Collation: utf8_general_ci
Create_options: row_format=COMPACT
Why does it so?
- MySQL 5.7
- InnoDB
P.S: I made the same test with MyISAM and got expected benefit: table with latin1 - 383Mb, utf8 - 1Gb. But why it does not work for InnoDB?