SQLite3 日付で抽出する処理速度比較

Laravel5.5で

Schema::create('room_stocks', function (Blueprint $table) {
$table->increments('id');
$table->integer('room_id');
$table->date('date');
$table->integer('stock');
$table->integer('booked');
$table->boolean('stop')->default(false);

$table->timestamps();
$table->index(['room_id', 'date']);
});

(注目は「$table->date(‘date’);」)

結果

sqlite> .schema room_stocks
CREATE TABLE "room_stocks" (
"id" integer not null primary key autoincrement,
"room_id" integer not null,
"date" date not null,
"stock" integer not null,
"booked" integer not null,
"stop" tinyint(1) not null default '0',
"created_at" datetime null,
"updated_at" datetime null);
CREATE INDEX "room_stocks_room_id_date_index" on "room_stocks" ("room_id", "date");


日付の文字列表現（？）

抽出するプログラム

$time_start = microtime(true);
$query = App\RoomStock::whereBetween('date', ['2012-1-1','2012-2-1'])->where('room_id', '45');
Log::debug($query->toSql());
$stocks = $query->get();
$return = view('app', compact('setting', 'stocks'));
$time = microtime(true) - $time_start;
Log::debug($time);

結果

[2018-03-02 16:52:35] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 16:52:35] local.DEBUG: 0.090837955474854
[2018-03-02 16:53:01] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 16:53:01] local.DEBUG: 0.070255994796753
[2018-03-02 16:53:02] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 16:53:02] local.DEBUG: 0.063132047653198

0.05~0.09秒程度
だがしかし、、2012年12月も拾ってきてしまう。。dateフィールドは恐らくstring。

日付を文字列で保持（改）

データ投入時に

$date_str = sprintf("%d-%02d-%02d", $test_date[0], $test_date[1], $i);

としてデータ作成

[2018-03-02 17:15:32] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:15:32] local.DEBUG: 0.054426908493042
[2018-03-02 17:15:33] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:15:33] local.DEBUG: 0.096708059310913
[2018-03-02 17:15:34] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:15:34] local.DEBUG: 0.067840099334717

今回はきちんとデータ取得できた。

日付を文字列で保持（インデックスなし）

インデックスを付けないと結構遅い（総レコード数25,565）

[2018-03-02 17:25:01] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:25:01] local.DEBUG: 0.17413401603699
[2018-03-02 17:25:08] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:25:08] local.DEBUG: 0.13845801353455
[2018-03-02 17:25:09] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:25:09] local.DEBUG: 0.19870400428772
[2018-03-02 17:25:11] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:25:11] local.DEBUG: 0.15935492515564
[2018-03-02 17:25:12] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:25:12] local.DEBUG: 0.17379808425903


タイムスタンプを文字列で保持

データ投入時strtotimeでタイムスタンプに

$date_str = sprintf("%d-%02d-%02d", $test_date[0], $test_date[1], $i);
$stock->date = strtotime($date_str);

インデックスを付けないと結構遅い（総レコード数25,565）

[2018-03-02 17:42:27] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:42:27] local.DEBUG: 0.055305004119873
[2018-03-02 17:42:35] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:42:35] local.DEBUG: 0.053861141204834
[2018-03-02 17:42:36] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:42:36] local.DEBUG: 0.055207967758179
[2018-03-02 17:42:37] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 17:42:37] local.DEBUG: 0.052020072937012

気持ち速いが、出力されるデータ（date）はタイムスタンプの数字。。

タイムスタンプを整数で保持


Schema::create('room_stocks', function (Blueprint $table) {
$table->increments('id');
$table->integer('room_id');
$table->integer('date');
$table->integer('stock');
$table->integer('booked');
$table->boolean('stop')->default(false);

$table->timestamps();
$table->index(['room_id', 'date']);
});

注目すべきは「$table->integer(‘date’);」

[2018-03-02 18:00:52] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 18:00:52] local.DEBUG: 0.079493999481201
[2018-03-02 18:00:53] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 18:00:53] local.DEBUG: 0.079170942306519
[2018-03-02 18:00:55] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 18:00:55] local.DEBUG: 0.075047969818115
[2018-03-02 18:00:57] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 18:00:57] local.DEBUG: 0.07608699798584
[2018-03-02 18:01:01] local.DEBUG: select * from "room_stocks" where "date" between ? and ? and "room_id" = ?
[2018-03-02 18:01:01] local.DEBUG: 0.070191860198975

おや、少し遅くなった。。

結論

タイムスタンプで保持しても劇的に速くなるわけではなさそうなのでその後の処理が楽そうな日付（SQLiteは恐らく文字列で保持）で良さそう。
ただしゼロ埋めする必要がある。

Tweet