0

  1. Is it smart to have one column set to UTF-8 and to leave rest in latin1_ . Will that affect performance of queries (maybe some conversions will be called)? Or should I set all to UTF-8. (Latin columns are generated by application and are all in latin1_ so no reason to put them in utf-8, and also i have more space in VARCHAR witch is important in this case, I would not like to switch to TEXT if I do not have to ). Php is only calling INSERT, SELECT and UPDATE on that table. Maybe to divide that table to two tables witch leads me to second question.

  2. If php mysqli->connection charset is set to UTF-8 and is SELECTING, INSERTING and UPDATING a table set to latin1_, can I leave it like that or should I change it to utf-8, regarding performance(are there any conversion functions called even thou all latin1_ columns are generated by application and are in latin1_, also VARCHAR needs to switch to TEXT )

Dime
  • 153
  • 9

1 Answers1

1

If you use mysqli::set_charset() to set the client's charset to UTF-8, then MySQL will expect all incoming queries to use that encoding. It will also serve all results in UTF-8, including the values from the columns that use latin1 charset.

The conversions are done automatically and will result at least some overhead.

Everything will be fine as long as the application sends and expects UTF-8 everywhere. If not, you're going to have a bad time.

Is it smart to mix encodings like that? No, I don't believe so.

Shira
  • 6,392
  • 2
  • 25
  • 27
  • Would you then recommend changing VARCHAR to TEXT fields and converting whole table to UTF-8. – Dime Dec 11 '16 at 01:03
  • 1
    Why would you change VARCHAR to TEXT? For example VARCHAR(32) will support 32 characters regardless of the used charset, unless you are using a very old version of MySQL. http://stackoverflow.com/questions/1997540/mysql-varchar-lengths-and-utf-8 – Shira Dec 11 '16 at 01:10
  • Problem is that it is very precisely calculated and I need two fields VARCHAR (20 000) and VARCHAR (40 000) So UTF-8 can't support that ( i am limited with size in bytes). Max VARCHAR for a table is ~21000 If UTF-8 is used ! So the quetion would be change that to TEXT UTF-8 or leave it as latin VARCHAR and have automatic conversions, what would be faster to use ? (i have 5.6.34 mysql version) – Dime Dec 11 '16 at 02:34
  • 1
    I'd use TEXT for text fields that big. – Shira Dec 11 '16 at 10:16