おはようございます、平塚です。

On Wed, 11 Apr 2012 18:24:05 +0900
中川 貴<takashi.nakagawa@xxxxxxxxxx> wrote:

> 確信はありませんが、これが影響している可能性はあります。
> 私も日本郵政の郵便番号辞書のデータでutf8とcp932のテーブルを作成して
> JOINしてみましたが副次キーの場合、
> ジョインに索引が使われませんでした。

(2012/04/11 17:03), ochiai wrote:

> > 今気づいたことですが、手違いでtable-aとtable-bのキャラクタセットが
> > 異なっていました。本来はcp932で統一します。
> > これは影響するでしょうか？

こちらでも再現しました。
原因は中川さんのおっしゃる通りで間違いないと思います。
解決策としては、キャラクタセットを統一してください。

==

以下解説です。

FROM ta LEFT JOIN tb ON ta.c1 = tb.c1

というSQLにおいてtaがutf8、tbがcp932の場合、
内部的に以下のような変換が行われ、utf8同士での比較となります。

ta.c1 = CAST(tb.c1 AS CHAR CHARACTER SET utf8)

tb.c1に対して関数を適用しているため、
tb.c1にインデックスがあっても使えなくなります。

--

もし問題のSQLがINNER JOINであれば、
tbを駆動表としてta.c1のインデックスを使う
次善のSQL実行計画になった可能性があります。

しかしLEFT JOINですと
tbに該当なしのデータも引き抜いてくる必要があるため
taを駆動表としたSQL実行計画しか選べず、
フルスキャンやむなしとなります。

--

また、taがcp932、tbがutf8という逆のパターンでは

CAST(ta.c1 AS CHAR CHARACTER SET utf8) = tb.c1

となるため、taが駆動表である場合に
tb.c1のインデックスを使うことができます。

このときtbのrefに「func」が現れます。
ta.c1にfuncを適用したものとtb.c1をJOINするという意味です。

--

おまけでtaがcp932、tbがeucjpmsの場合は、
暗黙のキャラクタセット変換ができずエラーとなります。

ERROR 1267 (HY000): Illegal mix of collations
(cp932_japanese_ci,IMPLICIT) and (eucjpms_japanese_ci,IMPLICIT)
for operation '='

暗黙のキャラクタセット変換ができるのは、調べた限り
cp932   → utf8
eucjpms → utf8
utf8    → utf8mb4
などutf8に向かう方向のみのようです。(latin1 → cp932は不可)

-- 
平塚貞夫 hiratsuka.sadao@xxxxxxxxxx