maria-discuss team mailing list archive

["Reinis Rozitis" <r@xxxxxxx>]

Hello,
before reporting it as bug I wanted to understand what's really going on or how to investigate it further:

Because of the DST changes we experienced a strange issue where afterwards a query suddenly wouldn't use an index on a timestamp column anymore and would do a full tablescan and because of the table size literally brought down the db.




The table itself is more complex but I could simplify it down to:

CREATE TABLE `tmp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `ts_2` (`ts`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


(the original table is TokuDB but the same behavior happens also on InnoDB / Server Mariadb 10.3.13):


And the issue is:

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


While actually:

MariaDB [db]> select count(*) from tmp where `ts` > '2019-03-31 03:00:00';
+----------+
| count(*) |
+----------+
|      175 |
+----------+


So either the sql optimizer or the engine does something strange.


While for example:

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

.. works as expected.



The data is basically (just missing the 3:xx because of the clock spin):

| 35458195 | 2019-03-31 02:55:44 |
| 35458196 | 2019-03-31 02:55:45 |
| 35458197 | 2019-03-31 02:56:37 |
| 35458198 | 2019-03-31 02:59:16 |
| 35458199 | 2019-03-31 02:59:39 |
| 35458200 | 2019-03-31 04:01:42 |
| 35458201 | 2019-03-31 04:02:59 |
| 35458202 | 2019-03-31 04:04:02 |
| 35458203 | 2019-03-31 04:04:26 |
| 35458204 | 2019-03-31 04:04:38 |


In the Innodb case the explain while showing that it will return all rows (the test table has 1000) also showing that it will use the 'ts_2' index on the original tokudb table it won't use the index even forced:

MariaDB [db]> explain select * from twitt_ext_posts_url where `ts` > '2019-03-31 04: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 | 42444 | Using where |
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------+

.. works again fine, but:


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 | 28547845 | Using where |
+------+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+

MariaDB [db]> explain select * from twitt_ext_posts_url force index(ts_2) 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 | 28547872 | Using where |
+------+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+


Is this to be expected or a bug? Should a gap in index values drop the index use for range queries?
Is there anything else I need to add to the report?

rr