Does character set and collation affects query performance in MySQL/MariaDB?

user_4299415 0
Viewed 1642

I have been working on a database with large and expanding database in MariaDB. The database is accessed by a PHP application.

I set the DB character set and collation to utf8mb4 and utf8mb4_unicode_ci

But not all my tables has text (varchar, text etc.) columns. Some tables holds only relations, thus all columns are number types (int, bigint etc) or date/time etc. additionally but not text types.

In tables, where I only keep numbers, do I need to keep the unicode multi-byte character set/collation or I can select something else?

Most importantly does this affect the query performance?

mariadb query-performance mysql php
2 Answers

Even though your table has a collation and character set, the columns with numeric values do not need any collation and character set definition. Hence, it's irrelevant to consider selecting anything like that. You can create a table like below without defining collation for numeric columns:

CREATE TABLE t1
(
    c0 INT NOT NULL,
    c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

Yes, it does affect query performance. Please know that a character set is essentially a single character being saved with a different number of bytes. If there are only English alphabets, usually Latin is the best character set. Also, based on your MySQL version, different character sets give different performance. Please refer to this article as well. This might help.

https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance/

user_96901510

The things that affect performance, approximately in order:

  • Poor indexing
  • Poor formulation of queries
  • Disk I/O
  • The number rows that need to be read to satisfy a query
  • At the bottom of this list: expressions, functions, character set, collations, etc.

That is, Use the character sets and collations you need; don't worry about performance.

user_17668310
Related