新記事:[MySQL] 日付比較や日付検索が遅いのでBETWEENで改善させる
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となり、不可wwwww
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を使うと高速化できるのは分かるが、コードが長くなるし、直感的でもないし、スマートじゃないね...
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となり、不可wwwww
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を使うと高速化できるのは分かるが、コードが長くなるし、直感的でもないし、スマートじゃないね...
[PR] au PAY / au WALLET カード 情報
コメント
コメント一覧 (1)
翌月の一日未満 じゃだめなんですかね。