Re: 4.1 ten times slower when using utf8 charset

[Harrison Fisk]

Hi,

I see that your queries are totally dependant on string comparison time. You still didn't mention what COLLATION you are using for the UTF8 data. The utf8_unicode_ci collation uses contractions so it is quite slow. Try testing with utf8_general_ci or utf8_bin as both of those are much simpler and should be faster (utf8_bin would be the fastest afaik). It will still be slightly slower than latin1, but shouldn't be too much slower. INSTR and LOCATE shouldn't be any slower.

With that said you might be able to still use MySQL to convert to SJIS, even if you have the data stored as latin1. To do so you would have to use CAST to convert to binary, and then into SJIS. I haven't tested it though, so it might not work how I expect. The code would look something like:

CONVERT(CAST(col AS binary) USING sjis)

Hope one of the above helps!

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com