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
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
