I noticed a significant drop in performance after upgrading MariaDB from 10.2.19 to 10.3.12.
A simple query over a BIGINT UNSIGNED column (PK) takes less than 1ms on MariaDB 10.2.19 but more than 30ms on MariaDB 10.3.12.
This occurs when the condition (using the IN clause) exceeds the maximum value of BIGINT (signed!), that is 9223372036854775807
CREATE TABLE `tt` (
|
`id` bigint(20) unsigned NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
Table tt contains about 100,000 rows.
MariaDB [np]> SELECT MIN(id), MAX(id), COUNT(*) FROM tt;
|
+--------------+--------------------+----------+
|
| MIN(id) | MAX(id) | COUNT(*) |
|
+--------------+--------------------+----------+
|
| 514477679209 | 696605562020073273 | 100001 |
|
+--------------+--------------------+----------+
|
1 row in set (0.033 sec)
|
MariaDB 10.2.19
Response time is ok
MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807);
|
Empty set (0.00 sec)
|
|
|
MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
|
Empty set (0.00 sec)
|
|
|
MariaDB [np]> explain SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | tt | range | PRIMARY | PRIMARY | 8 | NULL | 2 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
MariaDB 10.3.12
Poor response time for values greater than 9223372036854775808
MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807);
|
Empty set (0.000 sec)
|
|
|
MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
|
Empty set (0.034 sec)
|
|
|
MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
|
Empty set (0.068 sec)
|
|
|
MariaDB [np]> explain SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
|
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|
| 1 | SIMPLE | tt | index | PRIMARY | PRIMARY | 8 | NULL | 100275 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|
1 row in set (0.001 sec)
|
