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