0

I am joining two tables on varchar columns having CHARACTER SET utf8 COLLATE utf8_bin and one with CHARACTER SET latin1. Indexing is not working when i am joining these two tables on this column, I am only storing alphanumeric values english characters a-z, A-Z and digits 0-9, no lengthy strings (chinese characters/UTF16) or characters that takes more space . I am working with Mysql InnoDB.

is this the normal behaviour?

Improve this question
2
  • is this the normal behaviour? Yes. Joining needs in string compare which needs in the same character set for definite collation to be applied. I.e. by fact the server uses joining by a function, like ON t1.col1 = CONVERT(t2.col2 USING charset). So index cannot be used.
    – Akina
    Jun 6 at 10:10
  • Since no index can be used, performance may suffer, especially for large tables.
    – Rick James
    Jun 6 at 20:21

1 Answer 1

Reset to default
0

if you can be sure the join order, for example, a left join b, you can convert the column's character in a (in the query), make it the same to the column in b,then you can use the index in b.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.