MySQL: COLLATE performance

collationMySQLperformancequery-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.

Best Answer

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.

Related Solutions

Mysql – What would make a MySQL database create tables in a different collation other than its default

Finally actually adding this to the question. You must specify the COLLATION while creating the table. It will inherit the collation from the database which is latin1_swedish_ci. Wordpress sets the collation only for the connection, not for the database. E.g. for WHERE or ORDER BY clauses... See Answer and comment similar to this in link above.

hrake gets the credit not I

SQL Server Collation – Using COLLATE: When to Use and When Not to Use

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases.

In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables:

SELECT c.name, c.collation_name
FROM sys.all_columns AS c
INNER JOIN sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.name = N'sys' AND v.name = N'tables'
AND c.collation_name IS NOT NULL;

Results:

name                    SQL_Latin1_General_CP1_CI_AS
type                    Latin1_General_CI_AS_KS_WS
type_desc               Latin1_General_CI_AS_KS_WS
lock_escalation_desc    Latin1_General_CI_AS_KS_WS
durability_desc         Latin1_General_CI_AS_KS_WS

So here we have columns with two different collations inside a single system object. No database can have a DATABASE_DEFAULT that matches both...

You don't have any control over the collation of your customer's columns or databases, or the server collation. So really the only ways to resolve the conflict are to:

use a method that doesn't hard-code a specific collation (like DATABASE_DEFAULT)
hard-code some specific, compatible collation on both sides

Since the latter is more work, makes for more complex queries, and introduces more opportunities to turn seeks into scans, I think the former is really your best option.

Best Answer

Related Solutions

Mysql – What would make a MySQL database create tables in a different collation other than its default

SQL Server Collation – Using COLLATE: When to Use and When Not to Use

Related Question