中川様、平塚様

ありがとうございます。
私も先の返信をした後、キャラクタセットの不統一が影響しているかもしれない
と思い、、
ALTERでテーブルのデフォルトキャラクタセットをCP932に変更し、再度、
explainで様子を見ました。
結果は、変わりませんでした。

そこで、念のため、show create tableでテーブル定義を見たところ、
テーブルのデフォルトキャラクタセットはCP932に変更されていましたが、
varcharの各フィールドの定義にutf8が追加されていました。
既にデータが入っているフィールドのキャラクタセットは変更できないのかもし
れません。

そこで、最初からテーブルを作り直して、explainで様子をみました。
結果は、望んでいた結果となりました。
+----+-------------+---------+--------+---------------+-----------+---------+-----------------+--------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------+--------+---------------+-----------+---------+-----------------+--------+-------+

| 1 | SIMPLE | table-a | ALL | NULL | NULL | NULL | NULL | 220419 | |
| 1 | SIMPLE | table-b | eq_ref | F1_UNIQUE | F1_UNIQUE | 62 |
test.table-a.F1 | 1 | |
+----+-------------+---------+--------+---------------+-----------+---------+-----------------+--------+-------+


実際に、これでUPDATEを行ったところ、１分程度で処理が完了しました。

平塚様には、詳しい解説をいただき、納得しました。
キャラクタセットが異なる場合、暗黙のうちにCASTしてくれているとは、よく考えられていますね。
ところで、平塚さんの解説のような情報はどこにあるのでしょうか？よろしければ教えてください。

今回の教訓：デフォルトキャラクタセットはスキーマ単位で設定すると良い。

ありがとうございました。


(2012/04/11 18:24), 中川 貴 wrote:
> 確信はありませんが、これが影響している可能性はあります。
> 私も日本郵政の郵便番号辞書のデータでutf8とcp932のテーブルを作成してJOINしてみましたが副次キーの場合、
> ジョインに索引が使われませんでした。
> なぜだか分かりませんが、主キー同士の場合refに「func」表示が現れます。この場合、実際にどんな動作を
> しているかは分かりません。
> 試しに、charasetをcp932に合わせた環境でもう一度試して見てください。
>
> ****************************************
> コープ情報システム株式会社
> 運用事業部
> コーププラザサービスデスクグループ
> 中川　貴
> 175-0094
> 東京都渋谷区渋谷3-29-8 コーププラザ5F
> TEL 03-5778-8010
> E-MAIL takashi.nakagawa@xxxxxxxxxx
> ****************************************
>
> (2012/04/11 17:03), ochiai wrote:
>> 今気づいたことですが、手違いでtable-aとtable-bのキャラクタセットが異なっていました。本来はcp932で統一します。
>> これは影響するでしょうか？
>
>



----- Original Message ----- 
From: "HIRATSUKA Sadao" <hiratsuka.sadao@xxxxxxxxxx>
To: <ml@xxxxxxxxxx>
Sent: Thursday, April 12, 2012 9:46 AM
Subject: [mysql 15729] Re: Left Join句を付けたUPDATE処理時間が非常に長くかかって（長文）


> おはようございます、平塚です。
>
> 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
>
>
>
>
>