(2011/02/24 12:48), shin-1@xxxxxxxxxx wrote:
> みなさんこんにちは、nomotoです。
> お知恵を貸してください。
>
> CentOS環境のMySQL5.0.77で、片方のテーブルからもう片方のテーブルに
> 一致するレコードが存在しないレコードだけを抽出したく、
> 2つのテーブルをJOINして、合致しないレコードをNULL判定して
> 抜き出そうとしています。
>
> ただ、2項目で比較しないとレコード特定できないデータなため
> 2項目のANDでJOINしようとしているのですが、1項目のJOINなら良いの
> ですが、2項目を比較してJOINしようとするとIndexを使ってくれなく
> なり、処理に10分以上かかってしまいます。
> 1項目だけでJOINすれば数秒で終わるので、同程度の速度を期待して
> いるのですが、2項目だとあまりに遅すぎて実用に耐えられません。
>
> 複合Indexを張らなきゃダメという情報も読んでIndexは貼ってあるの
> ですが、何が原因でIndexが使われなくなるのでしょうか。
> 何かアドバイスいただきたく、よろしくお願いいたします。
>
> データ構成的にはMyISAMで、tableB（約 84,000件）とtableC（約 5,000件）
> があり、それぞれVARCHAR(20)のcode1,code2という項目に文字データが
> 入っていて、その2項目の文字データを両方つき合わせて、はじめて
> レコードの特定ができるデータ内容です。
> ※code1,code2は各テーブルでb_code1,b_code2という具合に少し名前が
> 違います。
>
> tableBは20フィールド程度で1レコードあたり数百バイト程度の
> 文字データのみ。tableCは数個の文字フィールドと、6〜8MB程度の
> バイナリデータの入ったmidiumblobの項目1個です。
>
> tableBのcode1,code2はほぼ全件バラバラ、code1もcode2もそれぞれ
> 重複は数百件程度です。
>
> tableCのcode1はほぼ全件バラバラ、code2は空文字''で登録されている
> データが9割以上で、code2に値が入っているレコードはごく少数しか
> ありません（今後、code2に値の付いたデータが増える予定）
> なので、現状ではcode1,code2で2テーブルをJOINすると一致するのは
> 数十件で、ほとんどが不一致になります。
>
>
> 原因究明の第一歩としてEXPLAINをとりました。
>
> 1項目でのJOINなら両テーブルともUsing Indexが出ていますが、
> 2項目のANDでJOINするとtableCにはIndexが使われてないようです。
>
> 両テーブルのcode1,code2は、複合Indexが必要だろうと
> (code1),(code2),(code1,code2)という3パターンのIndexをどちらにも
> 貼ってあります。
>
> 1項目だけのJoinならIndexが使われます。
> mysql> explain select count(*) from tableB left join tableC
>  on(c_code1=b_code1) where c_code1 is null;
> +----+-------------+--------+-------+-------------------+-----------+---------+-----------------+-------+-------------+
> | id | select_type | table  | type  | possible_keys     | key       | key_len | ref             | rows  | Extra       |
> +----+-------------+--------+-------+-------------------+-----------+---------+-----------------+-------+-------------+
> |  1 | SIMPLE      | tableB | index | NULL              | b_code1   | 63      | NULL            | 84147 | Using index |
> |  1 | SIMPLE      | tableC | ref   | c_code1,c_code1_2 | c_code1   | 63      | test.tableB.b_code1 |     1 | Using Where; Using index |
> +----+-------------+--------+-------+-------------------+-----------+---------+-------------------+-------+-------------+
> 2 rows in set (0.00 sec)
>
> 2項目AND条件でJOINするとtableCでIndexが使われません
>
> mysql> explain select count(*) from tableB left join tableC
>  on(c_code1=b_code1 and c_code2=b_code2) where c_code1 is null;
> +----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
> | id | select_type | table  | type  | possible_keys             | key       | key_len | ref                 | rows  | Extra       |
> +----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
> |  1 | SIMPLE      | tableB | index | NULL                      | b_code1_2 | 126     | NULL                | 84147 | Using index |
> |  1 | SIMPLE      | tableC | ref   | c_code1,c_code2,c_code1_2 | b_code1   | 63      | test.tableB.c_code1 |     1 | Using Where |
> +----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
> 2 rows in set (0.00 sec)
>
> このあたりを見ろなど、アドバイスをどうぞよろしくお願いいたします。
>
>
SQLにヒントを与えて

explain select count(*)」
from tableB USING index(b_code1_2)
left join
tableC USING index(c_code1_2)
on(c_code1=b_code1 and c_code2=b_code2) where c_code1 is null;

にして実行してみてください。