Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9449

Optimizer regression in 10.1.9

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.9, 10.1
    • Fix Version/s: 10.1.11
    • Component/s: Optimizer
    • Labels:
      None

      Description

      After the upgrade from 10.1.8 to 10.1.9 or 10.1.10, frequent queries involving index merge went crazy.

      Here is a repeatable test case :

      DROP TABLE if exists t;
      CREATE TABLE t
      (
      id bigint PRIMARY KEY AUTO_INCREMENT,
      a bigint,
      b int,
      c bigint,
      KEY(a), KEY(b), KEY(c)
      ) ENGINE = InnoDB;
       
      INSERT INTO t(a, b, c)
      VALUES (rand() * 44, rand() * 283, rand() * 131072);
       
      INSERT INTO t(a, b, c)
      VALUES (rand() * 44, rand() * 283, rand() * 131072);
       
      REPLACE INTO t(a, b, c)
      SELECT rand() * 44, rand() * 283, rand() * 131072
      FROM t t1,
      t t2,
      t t3,
      t t4,
      t t5,
      t t6,
      t t7,
      t t8,
      t t9,
      t t10,
      t t11,
      t t12,
      t t13,
      t t14,
      t t15,
      t t16,
      t t17;
      ANALYZE TABLE t;
       
      EXPLAIN
      SELECT DISTINCT c
      FROM t
      WHERE b = 5 AND a = 10\G

      10.1.8 :

      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: t
      type: index_merge
      possible_keys: a,b
      key: b,a
      key_len: 5,9
      ref: NULL
      rows: 10
      Extra: Using intersect(b,a); Using where; Using temporary
      1 row in set (0.00 sec)

      10.1.9 :

      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: t
      type: index
      possible_keys: a,b
      key: c
      key_len: 9
      ref: NULL
      rows: 131109
      Extra: Using where
      1 row in set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            wfong Will Fong
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration