Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.12
    • Fix Version/s: 10.3, 10.4
    • Component/s: Server
    • Labels:
    • Environment:
      Linux Debian 9.6 (Stretch) APT packages

      Description

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                nico Nicolas Payart
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: