ハードディスクメンテナンス ブログ

投稿記事数8,000以上!PC,カメラ,オーディオ,ラジオ,航空,旧ゲームなど

日付比較や日付検索が遅い場合の改善方法(BETWEEN,MySQL)

   

>>高還元のクレジットカード比較 期間限定のキャッシュバックなど!<<


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を使うと高速化できるのは分かるが、コードが長くなるし、直感的でもないし、スマートじゃないね…

—–

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は含まれてしまうのだ。

MySQL全機能バイブル ~現場で役立つAtoZ~
鈴木 啓修
技術評論社
売り上げランキング: 199,175
実践ハイパフォーマンスMySQL 第3版
Baron Schwartz Peter Zaitsev Vadim Tkachenko
オライリージャパン
売り上げランキング: 56,487



sha-bc@336×280

sha-bc@336×280


sha-bc@336×280

sha-bc@336×280

  関連記事

no image
DebianでのGRUBのインストール

update-grub や grub-install をせよと、このブログで書い …

no image
ApacheでのURL書換有効化(mod_rewrite,.htaccess,Debian7.4,Apache2.2.22)

(Debian7.4 , Apache2.2.22) http://xxx.xx …

no image
(在庫僅少)激安サーバー2機種、Linuxを入れてファイルサーバーやMySQLの学習用に!(富士通とHP)

2万円以下の激安サーバーを紹介しよう。 富士通 PRIMERGY MX130 S …

no image
Debian7.4でWEBサーバーを立てる(Apache,MySQL,PHP,phpMyAdmin,FTP)

Windows環境でもXAMPPを入れると環境が整うので(その端末なのでFTP不 …

no image
論理集合(和・積・差)

和:和集合:A union B (重なり合う部分は1回のみ含む) A∪B(Aカッ …

Ubuntu
Ubuntu(14.04 LTS)の電源の統計一覧とバッテリーの劣化 [CF-VZSU49,CF-R6,CF-R7,CF-R8]

Linuxは標準で、ここまで細かい電源の情報を得ることができる。 Ubuntuな …

no image
(NTP)サーバーの時刻自動調整(Debian7.4)

サーバーの内蔵時計は、一度合わせてもずれていく(パソコンでも同様)。 時刻がずれ …

WordPress
さくらインターネットからエックスサーバーへ?WordPressとデータベースの速度 Amazon S3との連携?

さくらインターネットは機能豊富で大容量かつ安価であるが、DB(データベース)が遅 …

no image
CPU対決!激重SQL編!(Phenom X3 8400 vs Core i5 650)

実行時間が70秒近くかかる激重クエリがある。 この実行に於ける、ハードウェアの違 …

twitter
Twitterのタイムラインをサイトに埋め込む方法 – コードをユーザー毎に変化させるには?

Twitterのタイムラインをサイトに埋め込むのには、Twitetrにログインし …