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

Question

メインとサブの2つのテーブルが会った時に片方の更新だけが対象時間に含まれいたデータだけ抽出したいのですが、安直にWHERE句で更新日付をORで比較すると件数が多くかつ検索順位的にmainの方がフルスキャンになってしまいます。
UNIONで対応して２回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

Comments

[Orlofsky]

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

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

Answer 1

mainテーブルの

• _type
• up_datetime
• del_flg

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

Answer 2

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)
**
この辺は実行計画で確認しながら、インデックスを変更してみて下さい。

Comments

[enigumalu]

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

[sazi]

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