日付の範囲検索で索引を有効にする方法

###前提・実現したいこと
MySQLで開始日・終了日を持つ予定データから特定の範囲を含むデータを抽出しようとしていますが索引がうまく使えずフルテーブルスキャンになります。
CREATE TABLE 予定 (
pk int(11) NOT NULL AUTO_INCREMENT,
開始日 DateTime NOT NULL,
終了日 DateTime NOT NULL,
PRIMARY KEY (pk)
) ENGINE=innodb;

例として10/28~10/31の範囲で検索する場合は以下のようなSQLで検索しています。
SELECT * FROM 予定 WHERE 開始日 <= '2017-10-31 00:00:00' AND 終了日 >= '2017-10-28 00:00:00';

索引として以下の設定をしたのですが、どの場合も範囲を変更するとフルテーブルスキャンになります。
①開始日のみ、終了日のみのインデックスをそれぞれ作成
②開始日と終了日の複合インデックスを作成
③終了日と開始日の複合インデックスを作成

###試したこと
ポイント型と主キーを持つテーブルをMyISAMで作成し空間インデックスを作成しました。
CREATE TABLE test (
pk int(11) NOT NULL AUTO_INCREMENT,
開始終了 point NOT NULL,
PRIMARY KEY (pk),
SPATIAL KEY test_index (開始終了)
) ENGINE=MyISAM;
下記のようなSQLを作成し主キーを抜き出しています。
set @g1=GeomFromText('Polygon((1509375600 1509116400,1509375600 9999999999,0 9999999999,0 1509116400,1509375600 1509116400))');
SELECT id FROM test WHERE MBRContains(@g1,開始終了);

性能的には問題なさそうですがこのような使い方をして問題が無いのでしょうか。
他に良い方法などあれがご教示お願いします。
よろしくお願いします。

行動規範の内容に同意します

回答1件

MySQL 5.7 で開始日, 終了日の複合インデックスを張ったところ、ちゃんとインデックススキャンになりました。

sql
1CREATE TABLE `予定` (
2  `pk` int(11) NOT NULL AUTO_INCREMENT,
3  `開始日` datetime NOT NULL,
4  `終了日` datetime NOT NULL,
5  PRIMARY KEY (`pk`),
6  KEY `開始日と終了日` (`開始日`,`終了日`)
7) ENGINE=InnoDB;

text
1mysql> EXPLAIN SELECT * FROM 予定 WHERE 開始日 <= '2017-10-31 00:00:00' AND 終了日 >= '2017-10-28 00:00:00' \G
2*************************** 1. row ***************************
3           id: 1
4  select_type: SIMPLE
5        table: 予定
6   partitions: NULL
7         type: index
8possible_keys: 開始日と終了日
9          key: 開始日と終了日
10      key_len: 10
11          ref: NULL
12         rows: 3
13     filtered: 33.33
14        Extra: Using where; Using index
151 row in set, 1 warning (0.00 sec)

たまにインデックスを張っても使ってくれないことがあるので、一通りのメンテコマンド(ANALYZE TABLE, OPTIMIZE TABLE) を実行してみてください。

またレコードが少ない等の各条件によってはテーブルスキャンした方が有利と MySQL が判断する場合があります。ためしに SELECT * FROM 予定 FORCE INDEX (`＜インデックス名＞`)... と使用するインデックスを強制してみてください。これでちゃんとインデックススキャンになるならしばらくそれで様子見かなーと。

投稿2017/11/24 11:26