UTF-8 vs Latin1 mysql, indexes not used on utf-8

Question

I have tried creating my mysql tables with both UTF-8 and Latin1 Char sets. When I use Latin1 my indexes are used, when I use UTF-8 indexes are not used when selecting/limiting records. Is there something I am missing with the Char sets that causes this to happen?

Cheers

Ke

Answer 1

Indexes can be used only when the expression's collation matches that on the indexed column.

If the expression's COERCIBILITY is lower than that of the column (that is 2), the column's collation is casted to that of the expression, and the index is not used.

Normally, literals have COERCIBILITY of 4 and user variables that of 3, so this should be not a problem.

However, if you mix different collations in a JOIN or UNION, the cast order is not guaranteed.

In this case you should provide explicit collation to the column your are casting (most probably, you want to cast latin1 to UTF8), and this should be the collation of the column you are casting to:

SELECT  *
FROM    utf_table
JOIN    latin_table
ON      utf_column = latin_column COLLATE UTF8_GENERAL_CI

Answer 2

I understand this now, the tables i was joining were not of the same char set

DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Once I changed these the indexes worked fine.

Answer 3

The indexes themselves are stored with the same encoding as the columns they are indexing. Comparing a UTF-8 character to a latin1 character cannot make use of the index, because it would need to convert both to the same encoding, since the index optimizations are performed on a byte level (and ß in latin1 has a different byte sequence than in UTF-8).