質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.61%

SQL片方のテーブルの更新時間が条件内の抽出でORを使わない方法

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 299

enigumalu

score 190

メインとサブの2つのテーブルが会った時に片方の更新だけが対象時間に含まれいたデータだけ抽出したいのですが、安直にWHERE句で更新日付をORで比較すると件数が多くかつ検索順位的にmainの方がフルスキャンになってしまいます。
UNIONで対応して2回SQLが実行された方がそれぞれのindexに引っかかるのですが、他に対応方法はないでしょうか?
結合する際にsubテーブルのup_datetimeを結合条件にしても同じ結果が会えられるのかなと思っているのですが、整理出来ず質問させていただきました。

SELECT
    main._id
FROM
    t_main main
    LEFT OUTER JOIN
        t_sub sub
    ON  sub._id = main._id
    AND sub.del_flg = '0'
WHERE
    main.del_flg = '0'
AND main._type IN ('1', '2', '3')
AND (
    TIMESTAMP ('20191231000000') <= main.up_datetime
    OR TIMESTAMP ('20191231000000') <= sub.up_datetime
)
AND (
    main.up_datetime <= TIMESTAMP ('20191231235900')
    OR sub.up_datetime <= TIMESTAMP ('20191231235900')
)


main pk=_id index=up_datetime

_id _type up_datetime del_flg
111 1 20191231000000 0
222 2 20191231000000 0
333 3 20191222000000 0

sub pk=_id

_id up_datetime del_flg
111 20191230000000 0
222 20191231000000 0
333 20191222000000 0

欲しい結果
111,222

explain追加
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    main    ALL    idx_t_main_up_date                 399394    Using where
1    SIMPLE    sub    eq_ref    PRIMARY    PRIMARY    71    _id    1    Using where

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2020/01/06 20:12

    差支えない範囲でCREATE TABLE文, CREATE INDEX文, テーブル内のデータを INSERT文と実行計画を提示されては?

    EXPLAINによるMySQLの実行計画の確認
    http://begirama.hatenablog.com/entry/2018/02/05/234409

    キャンセル

回答 2

0

greatest()least()を使用して記述を書き換えると以下の様になります。

SELECT  main._id
FROM  t_main main
      LEFT OUTER JOIN t_sub sub
      ON  sub._id = main._id
      AND sub.del_flg = '0'
WHERE main.del_flg = '0'
  AND main._type IN ('1', '2', '3')
  AND TIMESTAMP ('20191231000000') <= greatest(main.up_datetime, sub.up_datetime)
  AND least(main.up_datetime, sub.up_datetime) <= TIMESTAMP ('20191231235900')

検索順位的にmainの方がフルスキャンになってしまいます。

ですが、記述内容より、インデックスに必要な項目が含まれているかどうかの方が、根本的な問題じゃないかと思います。

少なくとも以下の項目は一つのインデックスに含まれている必要があります。
※項目の並び順も関係はしますが、質問内容では不明です。
**t_main(_id, _type, del_flg, up_datetime)
t_sub(_id, del_flg, up_datetime)
**
この辺は実行計画で確認しながら、インデックスを変更してみて下さい。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/01/07 10:50

    ありがとうございます。mainとsubどちらにもup_datetimeにインデックスを追加して見たのですが、explainで見るとやはりtype=ALLになっていました

    キャンセル

  • 2020/01/07 16:24

    インデックスの内容と実行計画を質問に追記して下さい。

    キャンセル

0

mainテーブルの

  • _type
  • up_datetime
  • del_flg

の3つに対して複合インデックスを貼ってみてください

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る