MySQLでdatetime型(日時)を日付で抽出するSQLの速度比較

『2014-07-26 08:47:13』のようにdatetime型でカラムに格納されている値を日付で抽出(SELECT)した時の速度を比較してみました。

サンプルテーブルは100,000レコード
抽出するdatetime型のカラムにはインデックスを張っています。
実行時間は10回平均です。

結果はほほ想像通りですが、参考として
同じ結果でもSQL文にはいくつもの書き方があり、書き方によって速度が20倍も違います。

[参考記事] 日付型のフォーマットにスラッシュを使ってはいけません(文字コードによって値が変わる)

datetime型を『2014-07-26』のように単純に比較すると『00:00:00』が補完されます。 

`sample_time` = '2014-07-26'
　　　↓と同じ扱い
`sample_time` = '2014-07-26 00:00:00'

以下、早い順です。

日時をBETWEENで指定する方法

クエリの実行時間 0.0187秒

SELECT * FROM `sample_table` 
WHERE `sample_time` BETWEEN '2014-07-26 00:00:00' AND '2014-07-26 23:59:59'

調べた中ではこれが一番早いです。
ただしうるう秒があるので1日が必ずしも23:59:59までではない。
今のところMySQLではうるう秒は扱えないし、うるう秒は世界標準時の23:59:60なので日本だと08:59:60となりは問題になりません。

[参考記事] うるう秒のはなし

サブクエリを使う方法(1)

クエリの実行時間 0.0272秒

SELECT * FROM (
  SELECT * FROM `sample_table`
  WHERE `sample_time` >=  '2014-07-26 00:00:00'
) AS a
WHERE `sample_time` <  '2014-07-27 00:00:00'

2014-07-26以降の件数が全100,000レコード中2,500件の場合です。
件数が絞られるので早いですが、日付条件を逆にすると4.6780秒と極めて遅くなります。

日時を比較演算子で指定する方法

クエリの実行時間 0.0395秒

SELECT * FROM `sample_table` 
WHERE `sample_time` >= '2014-07-26 00:00:00' AND `sample_time` < '2014-07-27 00:00:00'

BETWEENと違い条件が2つになるので少し遅くなります。

datetime型をdate型に変換する方法

クエリの実行時間 0.0750秒

SELECT * FROM `sample_table` 
WHERE DATE( `sample_time` ) = '2014-07-26'

ここからインデックスを張っているカラムを変換しているので、インデックスの意味がなくなります。

datetime型をSUBSTRINGで切り取る方法

クエリの実行時間 0.0804秒

SELECT * FROM `sample_table` 
WHERE SUBSTRING( `sample_time`, 1, 10 ) = '2014-07-26'

文字列型に変換された後に部分抽出し、文字列比較しています。

datetime型をDATE_FORMATで整形する方法

クエリの実行時間 0.0851秒

SELECT * FROM `sample_table` 
WHERE DATE_FORMAT( `sample_time`, '%Y-%m-%d' ) = '2014-07-26'

日付のフォーマットで文字列型に変換し、文字列比較しています。
SUBSTRINGとDATE_FORMATの処理速度の差が出ています。

サブクエリを使う方法(2)

クエリの実行時間 4.6780秒

SELECT * FROM (
  SELECT * FROM `sample_table`
  WHERE `sample_time` <  '2014-07-27 00:00:00'
) AS a
WHERE `sample_time` >=  '2014-07-26 00:00:00'

2014-07-27以前の件数が全100,000レコード中97,500件の場合です。
サブクエリの外ではインデックスが無効になっているので遅くなります。

LIKEを使う方法

クエリの実行時間 0.4726秒

SELECT * FROM `sample_table` 
WHERE `sample_time` LIKE '2014-07-26%'

LIKE検索は遅いです。

参考値

クエリの実行時間 0.0008秒

SELECT * FROM  `sample_table`
WHERE `sample_time` >=  '2014-07-26 00:00:00'


2,500レコードです。




クエリの実行時間 0.0649秒


SELECT * FROM  `sample_table`
WHERE `sample_time` <  '2014-07-27 00:00:00'




97,500レコードです。





関連記事













        


スポンサーリンク

改行コードのCRとLF (キャリッジは印字ヘッドのことではありません)

ホームページ製作・web系アプリ系の製作案件募集中です。

上に戻る