CODEWITHSUNDEEP
mysql
Sander Visser
Sander Visser

Reputation: 4330

MYSQL COLLATE Performance

I have a table called users with a column firstname and with collation utf8_bin

I want to know what happens under the hood when I execute an query like

SELECT * FROM `users` `a` WHERE `a`.`firstname` = 'sander' COLLATE utf8_general_ci

the column firstname isn't an index, what happens with the performance when the command executed?

And what if the default collation was utf8_general_ci and the query is executed without COLLATE

I want to know the impact it has on a big table (8 million+ records)

Upvotes: 1

Views: 3012

Answers (1)

eggyal
eggyal

Reputation: 126035

In this case, since the forced collation is defined over the same character set as the column's encoding, there won't be any performance impact.

However, if one forced a collation that is defined over a different character set, MySQL may have to transcode the column's values (which would have a performance impact); I think MySQL will do this automatically if the forced collation is over the Unicode character set, and any other situation will raise an "illegal mix of collations" error.

Note that the collation recorded against a column's definition is merely a hint to MySQL over which collation is preferred; it may or may not be used in a given expression, depending on the rules detailed under Collation of Expressions.

Upvotes: 3

Related Questions