maria-discuss team mailing list archive

["Reinis Rozitis" <r@xxxxxxx>]

Hi,
I was playing more with the issue of Mariadb not using a timestamp index for a particular time range - can someone explain why the following happens (or how the timestamps actually work?):


db:~ # date
Wed Apr  3 18:17:36 EEST 2019

MariaDB [db]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

MariaDB [db]> explain select * from twitt_ext_posts_url where `ts` > '2019-03-31 03:00:00';
+------+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| id   | select_type | table               | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+------+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | twitt_ext_posts_url | ALL  | ts_2          | NULL | NULL    | NULL | 28468881 | Using where |
+------+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
(same happens with SET TIME_ZONE = 'Europe/Helsinki';)


then again:

MariaDB [db]> SET TIME_ZONE='+03:00';
MariaDB [db]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM             | +03:00              |
+--------------------+---------------------+

MariaDB [db]> explain select * from twitt_ext_posts_url where `ts` > '2019-03-31 03:00:00';
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table               | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | twitt_ext_posts_url | range | ts_2          | ts_2 | 4       | NULL | 55114 | Using where |
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------+

So 28 milion rows vs just 55k.


I understand that because of DST technically '2019-03-31 03:xx:xx' values for particular EEST timezone in real life can't happen but why is that a reason for the sql query planer to fail to use the index for the range and instead read the whole table? 

>From the programming perspective the application actually used something like 'ts > NOW() - 10 hours' so for one hour when it converted to 03:xx:xx the db server was burning down.

rr