Comments on: Understanding How MySQL Collation and Charset Settings Impact Performance

By: Gaetano Giunta

Gaetano Giunta — Tue, 02 Apr 2019 18:31:20 +0000

It would be interesting to know if there is any difference in performance, for MySQL 5.7, between the ‘historical’ 3 byte utf8_general_ci and the ‘modern’ utf8mb4.
This could be f.e. a driving factor in deciding about upgrading exiting databases (which have no stringent need to support characters outside the bmp at the moment)

By: Karl

Karl — Sun, 03 Mar 2019 08:35:29 +0000

In reply to Vadim Tkachenko. Ok I take it back, I don't actually join on varchars. However almost all our lookups are on secondary indexes on varchar(64) columns. That's just the way it has to be because this data comes from external sources. But the lookup speed / data set larger than RAM issue with indexes on varchar columns with different charsets is still something that would be very interesting to hear more about!

By: Vadim Tkachenko

Vadim Tkachenko — Sun, 03 Mar 2019 01:17:51 +0000

In reply to Karl. Karl, Using a string column to join tables is rarely a good idea in general. What kind of schema you have in mind to join on characters columns?

By: Karl

Karl — Sat, 02 Mar 2019 17:54:31 +0000

This is something I’ve thought a lot about recently when designing tables.

Since we can specify charset and collation per column, does it not make a lot of sense to be careful to set latin1 as the charset for varchar columns that are used either for joins or being indexed? This is of course only possible if we know that the column is ANSI only which it often is for many things such as guids and hashes.

We have a system with a lot of indexes and joins being done on varchars. That’s just the nature of what’s being done and can’t really be helped by normalization, at least not with a performance benefit. It’s obvious that indexes are a lot larger with utf8mb4 meaning that (I assume) a lot less data can be kept in RAM. It’s interesting that MySQL chose this default, is it really that big a problem that people can’t store emoticons into their tables by default compared to performance in high usage scenarios?

It would be very interesting to see benchmarks on the impact of index size, performance with data size larger than RAM and join performance on latin1 vs utf8mb4.

By: vadimtk

vadimtk — Fri, 01 Mar 2019 17:51:53 +0000

In reply to Camille Huot (@CamilleHuot). Camille, You can set latin1 in MySQL 8 in different ways. 1. Global , in my.cnf character_set_server=latin1 collation_server=latin1_swedish_ci 2. Per table. CREATE TABLE t (c CHAR(20) CHARACTER SET latin1 3. Per column col1 VARCHAR(5) CHARACTER SET latin1

By: Camille Huot (@CamilleHuot)

Camille Huot (@CamilleHuot) — Fri, 01 Mar 2019 07:39:03 +0000

Interesting results. I guess that the performance drop comes from additional convertions, do you confirm?
So what is the right setup if I need latin1 on mysql8?