Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
