1

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.

1 Answer 1

2

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 (versus defining that collation as the column's default; whereas utf8_general_ci will almost certainly perform slower in comparisons than utf8_bin due the extra lookups/computation required).

However, if one forced a collation that is defined over a different character set, MySQL would have to transcode the column's values (which would have a performance impact); I think it does this automatically if the forced collation is over the Unicode character set, or raises an "illegal mix of collations" error in any other circumstance.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.