[MariaDB] 日時(datetime型)を日付でSELECTするSQLの速度比較 [MySQL]

(2024年6月)

・サイトのアクセスログ(ログは常時増加)
・レコード数：708,971
・カラム`id`は一意であり、PRIMARYが設定されている。
・カラム`dt`にはdatetime型で年月日時分秒(例：2024-06-12 12:34:56)である。
・カラム`dt`は一意ではない。
・カラム`dt`には、indexが設定されている。
・カラム`bot`には、indexが設定されている。
・カラム`ati`には、indexが設定されている。
・カラム数は全部で14ある。
・`bot`や`ati`に値があると除外(双方ともint(1)で、値は1 か NULL)。

実行時間を調べる

(MariaDB 10.5.x)

MariaDBの場合、SELECTの前に「ANALYZE FORMAT=JSON」付加して実行時間を調べられる。

以下のSELECTの結果は、全て同じ「count=1647」である。

なお、MariaDB 10.5.xの場合、IS NULLやIS NOT NULLに対するインデックスは効く。

DATE_FORMAT

ANALYZE FORMAT=JSON SELECT COUNT(`id`) AS `count` FROM `log` WHERE DATE_FORMAT(`dt`,’%Y-%m-%d’) = ‘2024-06-11’ AND `bot` IS NULL AND `ati` IS NULL ;

・select_type：SIMPLE
・table：log
・type：ALL
・possible_keys：bot,ati
・key：NULL
・key_len：NULL
・ref：NULL
・rows：709112
・Extra：Using where

{
“query_block”: {
“select_id”: 1,
“r_loops”: 1,
“r_total_time_ms”: 320.4142981,
“table”: {
“table_name”: “log”,
“access_type”: “ALL”,
“possible_keys”: [“bot”, “ati”],
“r_loops”: 1,
“rows”: 709219,
“r_rows”: 719217,
“r_table_time_ms”: 166.9288551,
“r_other_time_ms”: 153.482597,
“filtered”: 24.99992943,
“r_filtered”: 0.228999036,
“attached_condition”: “date_format(log.dt,’%Y-%m-%d’) = ‘2024-06-11’ and log.bot is null and log.ati is null”
}
}
}

WHERE内に於ゐて左辺をDATE_FORMAT(日付および時間関数)でゐヂると、インデックスが効かない。

前方一致

ANALYZE FORMAT=JSON SELECT COUNT(`id`) AS `count` FROM `log` WHERE `dt` LIKE ‘2024-06-11%’ AND `bot` IS NULL AND `ati` IS NULL ;

・select_type：SIMPLE
・table：log
・type：ALL
・possible_keys：dt,bot,ati
・key：NULL
・key_len：NULL
・ref：NULL
・rows：709117
・Extra：Using where

{
“query_block”: {
“select_id”: 1,
“r_loops”: 1,
“r_total_time_ms”: 260.9165949,
“table”: {
“table_name”: “log”,
“access_type”: “ALL”,
“possible_keys”: [“dt”, “bot”, “ati”],
“r_loops”: 1,
“rows”: 709221,
“r_rows”: 719219,
“r_table_time_ms”: 156.5776788,
“r_other_time_ms”: 104.3348674,
“filtered”: 24.99992943,
“r_filtered”: 0.2289984,
“attached_condition”: “log.dt like ‘2024-06-11%’ and log.bot is null and log.ati is null”
}
}
}

前方一致なンで、インデックスが効くンでは？

BETWEEN

ANALYZE FORMAT=JSON SELECT COUNT(`id`) AS `count` FROM `log` WHERE `dt` BETWEEN ‘2024-06-11 00:00:00’ AND ‘2024-06-11 23:59:59’ AND `bot` IS NULL AND `ati` IS NULL ;

・select_type：SIMPLE
・table：log
・type：range
・possible_keys：dt,bot,ati
・key：dt
・key_len：5
・ref：NULL
・rows：3478
・Extra：Using index condition; Using where

{
“query_block”: {
“select_id”: 1,
“r_loops”: 1,
“r_total_time_ms”: 6.249954408,
“table”: {
“table_name”: “log”,
“access_type”: “range”,
“possible_keys”: [“dt”, “bot”, “ati”],
“key”: “dt”,
“key_length”: “5”,
“used_key_parts”: [“dt”],
“r_loops”: 1,
“rows”: 3478,
“r_rows”: 3478,
“r_table_time_ms”: 6.013584082,
“r_other_time_ms”: 0.229866214,
“filtered”: 25,
“r_filtered”: 47.35480161,
“index_condition”: “log.dt between ‘2024-06-11 00:00:00.000000’ and ‘2024-06-11 23:59:59.000000′”,
“attached_condition”: “log.bot is null and log.ati is null”
}
}
}

かなり速くナッテル！

BETWEEN + CAST

ANALYZE FORMAT=JSON SELECT COUNT(`id`) AS `count` FROM `log` WHERE `dt` BETWEEN CAST(‘2024-06-11 00:00:00’ AS datetime) AND CAST(‘2024-06-11 23:59:59’ AS datetime) AND `bot` IS NULL AND `ati` IS NULL ;

・select_type：SIMPLE
・table：log
・type：range
・possible_keys：dt,bot,ati
・key：dt
・key_len：5
・ref：NULL
・rows：3478
・Extra：Using index condition; Using where

{
“query_block”: {
“select_id”: 1,
“r_loops”: 1,
“r_total_time_ms”: 5.075342749,
“table”: {
“table_name”: “log”,
“access_type”: “range”,
“possible_keys”: [“dt”, “bot”, “ati”],
“key”: “dt”,
“key_length”: “5”,
“used_key_parts”: [“dt”],
“r_loops”: 1,
“rows”: 3478,
“r_rows”: 3478,
“r_table_time_ms”: 4.840651933,
“r_other_time_ms”: 0.227996502,
“filtered”: 24.99992943,
“r_filtered”: 47.35480161,
“index_condition”: “log.dt between ‘2024-06-11 00:00:00’ and ‘2024-06-11 23:59:59′”,
“attached_condition”: “log.bot is null and log.ati is null”
}
}
}