datetime型の列(reg_time)があるテーブルに対し、年月日指定をかけてデータを取り出す。
注：datetime型の例：2014-01-01 12:34:56
SELECT * FROM `テーブル名` WHERE `reg_time` LIKE ‘2014-01-01%’
これは非常に遅い。
reg_timeにindexを設定しても、indexが使用されないので無意味。
初心者が多用するLIKEなんか使うからだよ！ということで、dateを使うが、
注：dateはdatetime型(2014-01-01 12:34:56)から年月日(2014-01-01)を取り出す関数。
SELECT * FROM `テーブル名` WHERE date(`reg_time`) = ‘2014-01-01’
これも同様に遅い(indexも使用されない)。
この解決方法は、BETWEENを使用することである。
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘2014-01-01 00:00:00’ AND ‘2014-01-01 23:59:59’
これにより、高速化が可能。
EXPLAINで動作を見ると、typeがallからrangeになっていることが分かる。
typeは、テーブルに対してどのような方法でアクセスするのかを示すもの。
allはフルテーブルスキャンで、インデックスが全く使用されていないことを表す、最悪なモノだ。
一方rangeは、インデックスを用いた範囲検索である。
2014年1月1日の始端(00:00:00)と終端(23:59:59)を設定し、その間(BETWEEN)とすれば、同じものがSELECTでき、強引にBETWEEN化できるのは分かる。
まぁ、これはそれでよいとして、年月指定(日がない)の場合はどうか。
月毎の集計とかね。
当然、
SELECT * FROM `テーブル名` WHERE `reg_time` LIKE ‘2014-01-%’
は遅く、同様に
SELECT * FROM `テーブル名` WHERE LEFT(`reg_time`,’7′) = ‘2014-01’
や
SELECT * FROM `テーブル名` WHERE LEFT(`reg_time`,’8′) = ‘2014-01-‘
注：LEFT(`reg_time`,’7′)は、reg_timeの左から7文字を取り出す、つまり年-月を取得する関数。
も遅い。
となると、高速化が考えられるのはBETWEENとなるが、時刻の場合は終端が23:59:59で決まるが、月日の場合の終端日はどうするか？
終端日は、月によって異なるだろう？
まぁ、ダメだと思って
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘2014-01-01 00:00:00’ AND ‘2014-01-32 23:59:59′
とすると…
エラーにはならないが、返された結果が0となり、不可ｗｗｗｗｗ
32日は存在しない日だからだろう。
となると、その月の最終日(月末日,晦日)を求めなければならない。
PHPであれば、
cal_days_in_month(CAL_GREGORIAN,$month,$year)
という関数がある(年と月の順序に注意)。
これは月末を求める関数ではなく、その年月の日数($hikazu)を求めるものだ。
日数が分かるということは、最終日($last_day)は
$hikazu = cal_days_in_month(CAL_GREGORIAN,$month,$year) ;
$last_day = $year.”-“.sprintf(“%02d”,$month).”-“.sprintf(“%02d”,$hikazu) ;
として求められるので、
$from = $year.”-“.sprintf(“%02d”,$month).”-01 00:00:00″ ;
$to = $last_day.” 23:59:59″ ;
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘$from’ AND ‘$to’
とすればよい。
また、MySQLの日付/時刻関数にlast_dayというものがある。
これは、与えた年月日の月末(年月日)を返す。
last_day(‘2014-01-01’) → 2014-01-31
これを使うと、
$from = $year.”-“.sprintf(“%02d”,$month).”-01″ ;
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN CONCAT(‘$from’,’ 00:00:00′) AND CONCAT(last_day(‘$from’),’ 23:59:59′)
とすることができる。
BETWEENを使うと高速化できるのは分かるが、コードが長くなるし、直感的でもないし、スマートじゃないね…
—–
`reg_time`にはミリ秒は格納されていないので、23:59:59.x秒が範囲外になることはない。
—–
BETWEEN ‘2014-01-01’ AND ‘2014-01-01’ + interval 1 day ;
とすると短く書けるように思えるが、これは 2014-01-02 00:00:00 を含むので、厳密には不適。
実際に試してみる。
以下のようなテーブルがあり、カラム(reg_time)に以下が格納されている。
2014-01-01 23:59:59
2014-01-02 00:00:00
2014-01-02 00:00:01
2014-01-02 00:00:02
これに対し、
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘2014-01-01 00:00:00’ AND ‘2014-01-01 23:59:59’ ;
を投げると、結果は
2014-01-01 23:59:59
の1件が返されるが、
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘2014-01-01’ AND ‘2014-01-01’ + INTERVAL 1 day ;
を投げると、結果は
2014-01-01 23:59:59
2014-01-02 00:00:00
となり、2件返されてしまう。
+ INTERVAL 1 dayでは、
2014-01-02 00:00:00(翌日の00:00:00)
が含まれてしまうのでアウト。
データに「00:00:00」が記録されているかはその記録頻度にも寄るが、開発時点で含まれていなければ「問題ナシッ！」てコトで通ってしまいかねない(笑)
で、運用開始後、「00:00:00」が記録され、一致せずに「オカシーナー♪」となる。
なら、
SELECT * FROM `テーブル名` WHERE `reg_time` BETWEEN ‘2014-01-01’ AND ‘2014-01-01’ + INTERVAL 1 day – INTERVAL 1 SECOND ;
と書けばどうなるのかはシラン(SILANE)。
技術評論社
売り上げランキング: 199,175
オライリージャパン
売り上げランキング: 56,487
