LaravelのDBの日付関連メソッド

Laravelの日付関連メソッド

データ準備

下記SQLを実行し、userテーブルを作成する

CREATE TABLE `test_user`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `registered_date` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_user_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULR CHARSET=utf8mb4;

whereDate

// created_at(datetime型)のカラムが2021-11-01のレコードを取得する
User::whereDate('created_at', '2021-11-01')->get();

// 実際のクエリを確認する
User::whereDate('created_at', '2021-11-01')->toSql();
// SELECT * FROM user WHERE DATE('created_at') = '2021-11-01';

EXPLAINを実行してみると、created_atにはったインデックスは使われていない。

mysql> EXPLAIN SELECT * FROM test_user WHERE DATE(`created_at`) = '2021-11-27';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

whereMonth

// created_at(datetime型)が11月のレコードを取得する
User::whereMonth('created_at', '11')->get();

// 実際のクエリを確認する
User::whereMonth('created_at', '11')->toSql();
// SELECT * FROM user WHERE MONTH(`created_at`) = '11';

EXPLAINを実行してみると、created_atにはったインデックスは使われていない。

mysql> EXPLAIN SELECT * FROM test_user WHERE MONTH(`created_at`) = '11';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

whereDay

// created_at(datetime型)が1日のレコードを取得する
User::whereDay('created_at', '01')->get();

// 実際のクエリを確認する
User::whereDay('created_at', '01')->toSql();
// SELECT * FROM user WHERE DAY(`created_at`) = 1;

EXPLAINを実行してみると、created_atにはったインデックスは使われていない。

mysql> EXPLAIN SELECT * FROM `test_user` WHERE DAY(`created_at`) = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

whereYear

// created_at(datetime型)が2021年のレコードを取得する
User::whereYear('created_at', '2021')->get();

// 実際のクエリを確認する
User::whereYear('created_at', '2021')->toSql();
// SELECT * FROM user WHERE YEAR(`created_at`) = 2021;

EXPLAINを実行してみると、created_atにはったインデックスは使われていない。

mysql> EXPLAIN SELECT * FROM `test_user` WHERE YEAR(`created_at`) = 2021;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

whereTime

// created_at(datetime型)が14:29:57のレコードを取得する
User::whereYear('created_at', '14:29:57')->get();

// 実際のクエリを確認する
User::whereTime('created_at', '14:29:57')->toSql();
// SELECT * FROM user WHERE TIME(`created_at`) = '14:29:57';

EXPLAINを実行してみると、created_atにはったインデックスは使われていない。

mysql> EXPLAIN SELECT * FROM `test_user` TIME(`created_at`) = '14:29:57';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

まとめ

いずれの場合もカラムを変換しているため、インデックスを貼っていてもこれらのメソッドを使う場合はインデックスが使われないことが分かった。
パフォーマンス重視しない分析等で使用できたらいいかな。