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?

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, 2023 at 10:10
  • Since no index can be used, performance may suffer, especially for large tables.
    – Rick James
    Jun 6, 2023 at 20:21

1 Answer 1

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.

Your Answer

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

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