About indexes when join 2 columns with int type and varchar type

User.id type INT
Avatar.foreign_key type VARCHAR

When I use thi开发者_如何学Cs query:

SELECT *
FROM `user`
LEFT JOIN `avatar` ON (`avatar`.`foreign_key` = `user`.`id` )

Indexes don't use in this query. Unless I change User.id, Avatar.foreign_key to same type.

How can I make above query use index that I don't need to change 2 columns type? I want keep VARCHAR and INT on 2 columns.

They need to be the same type--and they should be anyway. If you need one of them to be a VARCHAR, and the other to be an INT, that's a sign that you're abusing the VARCHAR column. You should add a new INT column to your avatar table that can have a proper FK relationship to the other.

Why do you need one of these to be a VARCHAR?