MySQL Forums :: Newbie :: utf8 and latin1 table size


Advanced Search

utf8 and latin1 table size
Posted by: Shiv M ()
Date: July 07, 2011 03:04PM

Recently I converted a latin1 character set table to utf8 and to my surprise the size was almost 40% less and average_length was halved on doing so, when in fact I was expecting it to increase in size or at least same as before since latin1 characters map to utf8 single bytes retaining the same length. I am missing something here.

Thanks in advance for any details on what I see below.

Original table...latin1 charset

mysql> show table status like 'XYZ' \G;
*************************** 1. row ***************************
Name: XYZ
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2616427
Avg_row_length: 201
Data_length: 436207616
Max_data_length: 0
Index_length: 248381440
Data_free: 7340032
Auto_increment: NULL
Create_time: 2011-07-05 20:24:40
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)


mysql> ALTER TABLE XYZ CONVERT TO CHARACTER SET utf8;

mysql> show table status like 'XYZ' \G;
*************************** 1. row ***************************
Name: XYZ
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2613257
Avg_row_length: 103
Data_length: 270532608
Max_data_length: 0
Index_length: 147849216
Data_free: 5242880
Auto_increment: NULL
Create_time: 2011-07-07 20:24:40
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Options: ReplyQuote


Subject Written By Posted
utf8 and latin1 table size Shiv M 07/07/2011 03:04PM
Re: utf8 and latin1 table size Shiv M 07/08/2011 10:31AM
Re: utf8 and latin1 table size Rick James 07/08/2011 08:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.