CODEWITHSUNDEEP
mysqlasp.netutf-8character-encodingcollation
Prageeth Liyanage
Prageeth Liyanage

Reputation: 1772

Will different charsets and collations for database,tables and columns lead to performance issue?

I am developing a website by using ASP.net and My DB is MYSQL.

In there I do not want users to restricts only for post English contents to my website. So to support multilanguages I decided to use Charset as Utf8MB4 and collation as utf8_general_ci.

So now I have a few questions.

1) Shall I set database's charset as Utf8MB4 and collation for utf8_general_ci?

2) Use database's charset as Latin1 and collation as swedsh_ci and In table levels shall I make table for charset as Utf8MB4 and collation for utf8_general_ci?

3) Use database's charset as Latin1 and collation as swedesh_ci and In tables also use Latin1 and swedesh_ci and in columns shall I set charset as Utf8MB4 and collation for utf8_general_ci?

So I am confuse here. Is different charsets and collations lead to performance issues? What is the best approach?

Upvotes: 4

Views: 759

Answers (1)

O. Jones
O. Jones

Reputation: 108841

utf8mb4 and utf8_general_ci are fine choices for a multilingual application. It's a good idea to spell these things out for each table in your DDL (your table definitions) and avoid relying on defaults.

It's also a good idea to make sure your session's character-set value matches your tables. You can do this by setting the server-wide (not database) default, or by issuing this SQL command right after you connect from your program

  SET CHARACTER SET utf8mb4

In general, it's a good idea to configure your mysql server to hold server-wide defaults of utf8 or utf8mb4 and utf8_general_ci. That's explained here. http://dev.mysql.com/doc/refman/5.6/en/charset-applications.html

Indexes on columns match the column's collation. You can defeat index usage by specifying a specific collation on a query. For example, if your default collation is utf8_general_ci, but you want Spanish collation (which distinguishes N and Ñ) you can do this in your query.

ORDER BY surname COLLATE utf8_spanish_ci

This will yield correct results, but it won't be able to use an index on your column because the column's default collation is baked into the index.

Upvotes: 2

Related Questions