CODEWITHSUNDEEP
mysqlencoding
shantanuo
shantanuo

Reputation: 32316

Importance of Default charset

I have a 4 GB table and the default charset was set to utf8 though I am saving only latin1 characters. I changed it to latin1 using alter table statement on a test machine. The index file log_details.MYI was reduced by 5% while there was no difference noted in the data file, log_details.MYD

I have a few questions:

1) Should I alter the table on production? is it worth it?

2) Will it improve the select speed?

3) I guess I can have longer indexes once I change the default charset to latin1. Any other advantage?

I have also noted that after changing the default charset using alter table statement, the varchar columns types were changed automatically. Item_ID varchar(32) character set utf8 How do I avoid this?

mysql> create table char_test( id int, Item_ID varchar(32) ) default charset = utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into char_test values (1, 'abc');
Query OK, 1 row affected (0.00 sec)

mysql> show create table char_test\G
*************************** 1. row ***************************
       Table: char_test
Create Table: CREATE TABLE `char_test` (
  `id` int(11) default NULL,
  `Item_ID` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table char_test default charset = latin1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table char_test\G
*************************** 1. row ***************************
       Table: char_test
Create Table: CREATE TABLE `char_test` (
  `id` int(11) default NULL,
  `Item_ID` varchar(32) character set utf8 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Upvotes: 0

Views: 960

Answers (1)

Álvaro González
Álvaro González

Reputation: 146450

Performance is probably the last thing you should care about. What character set is you client-side app using? What natural language is the info written on? Those are the questions you must make.

If you stick to Latin1 you won't be able to store Japanese characters but also some common chars like the € symbol. On the other side, using UTF-8 in the database may be useless (or plain wrong) if your application cannot handle multi-byte input.

Upvotes: 1

Related Questions