About using UTF-8 fields in MySQL

https://www.adayinthelifeof.nl/2010/12/04/about-using-utf-8-fields-in-mysql/

 

I sometimes hear: “make everything utf-8 in your database, and all will be fine”. This so-called advice could not be further from the truth. Indeed, it will take care of internationalization and code-page problems when you use UTF-8, but it comes with a price, which may be too high for you to pay, especially if you have never realized it’s there..Indexing is everything… or at least.. good indexing makes or breaks your database. The fact remains: the smaller your indexes, the more index records can be loaded into memory and the faster the searches will be. So using small indexes pays off. Period.  But what has got this to do with UTF-8?

First off: beware of the VARCHAR

As you know, a VARCHAR field can hold a variable amount of data in which you only supply the maximum amount that you can store. So a VARCHAR(255) can hold 255 characters, but when you store only 5 characters, it will only use 5 characters of data. The other 250 are not lost. This is completely different than using a CHAR(255) where storing a 5 character string results in padding of 250 characters. So VARCHAR() has a big advantage over CHAR() when you have variable sized strings. But you have to realize that this advantage is for disk storage only. It does not apply to any other data structure that MySQL uses internally or for indexes.

How MySQL treats varchars

When MySQL needs to sort records, it must create some space for sorting that data. This space allocation is done before the actual sorting takes place. This however, means that MySQL needs to know how much memory it needs to allocate. When we need to sort VARCHAR fields, MySQL will take care of this by allocating the worst-case memory usage, which is the maximum size a VARCHAR field can take. For example: when you have declared a field as VARCHAR(100), MySQL will reserve space for 100 characters plus an additional 1 or 2 bytes for holding the length of the string (1 when the length is 255 or less, 2 otherwise). So this will bust the myth that “you can safely use VARCHAR(255)  for all fields without problems”.

Characters and bytes: or the UTF8-problem

Did you notice that I talk about “characters” and “bytes”? That’s because those two terms are not the same. A byte equals 8 bits, and can hold any number ranging from 0 to 255 (or -128..127, if you have read my two complement blog). The size of a character however, depends on the character encoding used and here is where the UTF-8 “problem” kicks in. Back in the old days, where most people stored strings in a latin1 charset, every character could be stored in a single byte. Thus: varchar(100) would be 100 bytes (+1 for the length). But this is not enough to hold ALL characters in the world (for instance, arabic and japanese characters cannot be stored in latin1). That’s why UTF-8 can use multiple bytes for some characters. The “standard” characters will be stored in 1 byte so most utf8 strings are almost the same size as latin1 strings, but when you need different characters it can use up to 4 bytes per character. If you like to know more about UTF-8, there are excellent other blogs about it.

You just have to realize that MySQL only uses a maximum of 3 bytes for UTF-8, which means not ALL utf-8 characters can be stored in MySQL, but most of the UTF-8 characters possible aren’t used anyway..  That’s why it might get confusing when reading upon UTF-8 that uses 4 bytes, and the 3 bytes that MySQL uses.

Let’s define a table with an index:

CREATE TABLE `tbl` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `first_name` varchar(100) character set latin1 collate latin1_general_ci NOT NULL,
 `last_name` varchar(100) character set latin1 collate latin1_general_ci NOT NULL,
 `birth_date` date NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `first_name` (`first_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This creates a simple table with a primary index on ID and only an index on ‘first_name’. You need to add at least 2 rows, otherwise the explain will not work correctly for this example. So add some data and  find out what index will be used when issuing the following query:

EXPLAIN SELECT * FROM tbl WHERE first_name LIKE 'joshua';

输出：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl   | range | first_name    | first_name | 102     | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

The most important field here is the key_len. This field is 102 bytes. 100 bytes for the VARCHAR(100), since it’s encoded with latin-1. The additional 2 bytes here are the length-bytes.

Now, let’s adjust the fields to UTF-8:

ALTER  TABLE  `tbl`  CHANGE  `first_name`  `first_name` VARCHAR( 100  )  CHARACTER  SET utf8 COLLATE utf8_general_ci NOT  NULL;

EXPLAIN SELECT * FROM tbl WHERE first_name LIKE 'joshua';

输出：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl   | range | first_name    | first_name | 302     | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Immediately you should see the impact. The key_len is 200 bytes larger, which means that we can hold less index-records in memory, which means more disk reads which means a slower database.

But it doesn’t stop at the indexes. As said, this limitation is for all internal buffers. All temporary sorting uses fixed length buffers and tables that are sorted in memory when using latin1, could just as easily be moved to a temporary table on disk because of it’s size. It WILL perform less efficient because of more disk reads and writes.

Conclusion:

MySQL and it’s internal working can be insanely complex. It’s important to never assume anything and test everything. Don’t convert everything to UTF-8 just because.. but make sure you have good reasons NOT to use a single-byte encoding like latin1. If you need to use the UTF-8 encoding, then make sure that you use the correct sizes. Don’t make everything VARCHAR(255) so at least you can store really long names. The penalties for “disrespecting” the database can and will be severe.. :)