こんにちは。
株式会社ビービットの玉越です。

ご返信ありがとうございます。

まず、MySQLは5.0.50です。

次に、無条件であればテーブルスキャンされて当然というご指摘は、おっしゃる通りです。
実際のクエリはもっと複雑な条件がつきますので、テーブルスキャンになりません。
実際、別々にクエリを作ってUNIONするのであればテーブルスキャンにはなりません。

具体的なデータ例も提示したいのですが、とても長くなるので少し整理させて下さい。
（実際のSQL文は700行以上あります）

日時の引き算の演算部分はインデックスが使われないというご指摘は認識しており、そこはインデックスは使われなくて大丈夫です。

問題はそこではなく、下記のようにまとめられます。
・構造は似ているが異なる二つのテーブルがある
・その二つのテーブルとJOINしつつ、条件に合致する行を一つだけ取得する必要がある
・そこで、一旦二つのテーブルをまとめた抽象的なテーブルを作り、それとJOINする
・ところが、それをすると元のテーブルにあったインデックスを使ってくれなくなる
・つまり、UNIONすると、元のインデックスを見てくれなくなるのではないか？という仮説を持っています。

上記の仮説が正しいのであれば、無理ではないかなあと考えています。

ご提案いただいているORDER BYとLIMITの組み合わせ、NOT EXISTSなどで取り出すというのは考えていませんでした。
ありがとうございます。
自分でも考えてみます。


2008/12/11 20:19 chuuken kenkou <ken_ken_1962@xxxxxxxxxx>:
>
> 「はてな」への質問は、約1年前のようですが、MySQLは4.1.22のままなのでしょうか？
>
> 検証するために、具体的なデータ例（ヒットすべきデータ、ヒットすべきでないデータ）を
> 提示できませんか？
>
> 無条件の検索結果をUNIONで繋ぐなら、テーブルスキャンされて当然だと思います。
> また、日時などの演算を検索条件に含めた段階で、MySQLではインデクスでの絞り込みが
> できないと思います。
>
> まだ具体的に考えた訳ではないですが、「直近の1件」というのを、「ORDER BYとLIMITを
> 組み合わせる」、「NOT EXISTSなどで取り出す」といった方法になるように感じます。
>
>
>
>
>> Date: Thu, 11 Dec 2008 19:49:28 +0900
>> From: hiroki.tamakoshi@xxxxxxxxxx
>> Subject: [mysql 14706] UNIONしたテーブルとJOINする際にインデックスが使われずテーブルスキャンになるのを回避する方法をご存じの方はご教示いただけますと幸いです。
>> To: ml@xxxxxxxxxx
>>
>> こんにちは。
>> 株式会社ビービットの玉越です。
>>
>> UNIONで悩んでいます。
>> たぶん解決不能と思うのですが、解決不能なのかどうかご存じの方はご教示いただけますと幸いです。
>> 長文で失礼致します。
>>
>> ■前提
>> 構造の異なるテーブルtableA, tableBがあります。
>> これらとtableCをJOINする必要があります。
>> インデックスは適切に張っています。
>>
>> tableA
>> id
>> C_id
>> time
>> description
>>
>> tableB
>> id
>> C_id
>> time
>> (←descriptionがない)
>>
>> tableC
>> id
>> time
>>
>> ■問題例1
>>
>> SELECT
>> union_table.description
>> FROM
>> ( SELECT
>> tableA.C_id,
>> tableA.description
>> FROM
>> tableA
>> UNION ALL
>> SELECT
>> tableB.C_id,
>> NULL
>> FROM
>> tableB
>> ) AS union_table,
>> tableC
>> WHERE
>> union_table.C_id = tableC.id
>>
>> union_table.C_idは、tableA.C_idかtableB.C_idのどちらかです。
>> なので、インデックスを効果的に使ってくれると期待します。
>> ところが、この方法ではtableA, tableBをテーブルスキャンするようです。
>>
>> ( .. UNION ALL .. )をすると、その中までは見てくれなくなるようです。
>>
>> http://q.hatena.ne.jp/1198431011にも同様のことがあります。
>>
>> ■解決案1
>>
>> 上記にある通り、下記のようにすればよいようです（これから実験します）
>>
>> SELECT
>> tableA.time
>> FROM
>> tableA,
>> tableC
>> WHERE
>> tableA.C_id = tableC.id
>> UNION ALL
>> SELECT
>> tableB.time
>> FROM
>> tableB,
>> tableC
>> WHERE
>> tableB.C_id = tableC.id
>>
>> 一度まとめテーブルを作るのではなく、結果をそれぞれ作ってからまとめる、
>> という方法です。
>>
>> ■解きたい問題
>>
>> 上記だけであれば問題は解決するのですが、私が行いたいのは下記です。
>>
>> SELECT
>> union_table.description
>> FROM
>> ( SELECT
>> tableA.C_id,
>> tableA.time,
>> tableA.description
>> FROM
>> tableA
>> UNION ALL
>> SELECT
>> tableB.C_id,
>> tableB.time,
>> NULL
>> FROM
>> tableB
>> ) AS union_table,
>> tableC
>> WHERE
>> union_table.C_id = tableC.id
>> AND tableC.time - union_table.time = (
>> SELECT
>> MIN( tableC2.time - union_table2.time )
>> FROM
>> ( SELECT
>> tableA.C_id,
>> tableA.time,
>> FROM
>> tableA
>> UNION ALL
>> SELECT
>> tableB.C_id,
>> tableB.time,
>> FROM
>> tableB
>> ) AS union_table2,
>> tableC AS tableC2
>> WHERE
>> union_table2.C_id = tableC.id
>> AND tableC2.id = tableC.id
>>
>> tableC.timeから見て、tableAとtableBのtimeのうち、一番近いものを一つだけ持ってくる
>> という操作です。
>>
>> これを解決案1のようにしてしまうと、別々に持ってきたものをUNION ALLするので、
>> 一つだけではなく二つ持ってきてしまうことになります。
>>
>> 解決は無理そうと思っていますが、もし可能だよ、という情報をご存じの方がいらっしゃいましたらご教示いただけますと幸いです。
>> また、不可能だよ、という情報ももしいただけますと、これ以上悩まなくて済むので同様にありがたいです。
>>
>> よろしくお願い致します。
>>
>>
>> --
>> -------------------------------------------------------
>> ◆ビービットはチームマイナス6%に参画しています◆
>> -------------------------------------------------------
>> 株式会社ビービット　玉越 大輝
>> ユーザビリティ コンサルタント
>> beBit,Inc. Tamakoshi Hiroki hiroki.tamakoshi@xxxxxxxxxx
>> --------------------------------------------------------
>>
>> ◆◆9月29日(月)より下記に移転いたしました◆◆
>>
>> 〒102-0071 東京都千代田区富士見2-14-37　FUJIMI EAST 1F
>> TEL: 03-5210-3891 / FAX: 03-5210-3895
>> URL: http://www.bebit.co.jp/
>> --------------------------------------------------------
>> ◆◆◆お知らせ◆◆◆
>> ・ユーザビリティ実践メモ(毎週月曜日更新)
>> http://www.bebit.co.jp/memo/
>>
>> ・ビービット書籍 『ユーザ中心ウェブサイト戦略』発売中
>> http://www.amazon.co.jp/gp/product/4797333529/
>>
> _________________________________________________________________
> 「ブリーフケースからお引越し」無料25GBのファイル保存サービス
> http://go.windowslive.jp/share/skydrive.html
>



-- 
-------------------------------------------------------
◆ビービットはチームマイナス6%に参画しています◆
-------------------------------------------------------
株式会社ビービット　玉越 大輝
ユーザビリティ コンサルタント
beBit,Inc. Tamakoshi Hiroki hiroki.tamakoshi@xxxxxxxxxx
--------------------------------------------------------

◆◆9月29日(月)より下記に移転いたしました◆◆

〒102-0071 東京都千代田区富士見2-14-37　FUJIMI EAST 1F
TEL: 03-5210-3891 / FAX: 03-5210-3895
URL: http://www.bebit.co.jp/
--------------------------------------------------------
◆◆◆お知らせ◆◆◆
・ユーザビリティ実践メモ(毎週月曜日更新)
 http://www.bebit.co.jp/memo/

・ビービット書籍 『ユーザ中心ウェブサイト戦略』発売中
 http://www.amazon.co.jp/gp/product/4797333529/