MySQLのgenerated columnを使ってOR検索を潰す

製品に販売期間設定がある場合に、販売可能なものを探すのに↓のようなクエリが発行されるとする
(valid_from と valid_to が null の場合は、期間の設定が特に設定されておらず期限のチェックが不要という意味)

SELECT
    *
FROM
    products
WHERE (valid_from < '2018-12-26'
    OR valid_from IS NULL)
AND (valid_to > '2018-12-26'
    OR valid_to IS NULL)

このとき valid_from と valid_to の複合インデックスがあっても、or検索があるため上手く使ってもらえない(と思う)
このorを消す方法を考える

valid_from、valid_to に NOT NULL制約をつけそれぞれに日付の最小値、最大値を入れることができればこの問題は解決できるが、既存のテーブルに値を追加していくのはDBでの対応以外にもアプリケーション側での対応も必要になる場合があり、すぐに対応ができないことがある。

そこで今回はgenerated columnを使う方法を試みる。

ALTER TABLE products ADD filled_valid_from datetime AS IFNULL(valid_from, '1000-01-01 00:00:00')
ALTER TABLE products ADD filled_valid_to datetime AS IFNULL(valid_to, '9999-12-31 23:59:59')

↑のクエリを投げると、AS 移行の計算結果が filled_hogehoge の中に入ることになる。これによりNULLがない日付型のカラムが利用できることになる。

SELECT
    *
FROM
    products
WHERE filled_valid_from < '2018-12-26' AND filled_valid_to > '2018-12-26'

というわけで↑のように OR 〜 IS NULL を消すことができた。

あとは↓のようにインデックスを貼ればよい。

ALTER TABLE products ADD INDEX idx_filled_valid_dates(filled_valid_from, filled_valid_to)

(ただこの例だと filled_valid_to が使われることはない?かもしれない)

やはりnull許容のカラムは悪い文明!!
粉砕する!!!

初稿時、不等号の向きとかがおかしかったので修正しました…