CODEWITHSUNDEEP
mysqlindexingunicodecollation
johnnyApplePRNG
johnnyApplePRNG

Reputation: 68

Is it more efficient to set more restrictive collations in MySQL columns that do not require utf8 characters?

Creating a new table where I want a few VARCHAR columns to be indexed, but only one of them really needs utf8_unicode_ci collation.

Would it be any more efficient when searching the table to only set that single column to utf8_unicode_ci collation?

Or is it the same if I collate the entire table with utf8_unicode_ci collation?

Upvotes: 0

Views: 34

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

If you use a binary collation, string comparisons are a little bit faster.

When you use a collation like utf8_unicode_ci, MySQL must do string comparisons character by character, so it can tell if each character is equivalent according to the rules in the collation you use.

But with a binary collation, MySQL is optimized to use the system call memcmp() to compare the whole string as literal bytes. This means there are no character equivalency rules. This comparison is case-sensitive.

However, the performance advantage of this is minor, and it's much more of an advantage to use an index.

Upvotes: 2

Related Questions