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

ORDER BY optimizer can't use Extended keys for partitioned table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5, 10.6
    • Fix Version/s: None
    • Component/s: Optimizer
    • Labels:
      None

      Description

      create table t1 (
        id int auto_increment,
        created_at datetime,
        key1 int,
        key2 int,
        key (key1),
        primary key(id,created_at)
      ) partition by range columns(created_at) (
       PARTITION `p1` VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION `p2` VALUES LESS THAN ('2015-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION `p3` VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION `p4` VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB
      );
      

      insert into t1 (created_at,key1,key2) 
      select 
        date_add('1995-01-01', interval seq day),
        seq,
        seq
      from seq_1_to_10000;
      

      explain 
      select * 
      from t1 ignore index(primary) 
      where key1=100  order by id limit 10;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
      |    1 | SIMPLE      | t1    | ref  | key1          | key1 | 5       | const |    4 | Using where; Using filesort |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
      

      The optimizer can't use index key1 to resolve the ORDER BY.

      Try the same without partitioning:

      create table t2 (
        id int auto_increment,
        created_at datetime,
        key1 int,
        key2 int,
        key (key1),
        primary key(id,created_at)
      );
      insert into t2 select * from t1;
      

      explain 
      select * 
      from t2 ignore index(primary) 
      where key1=100  order by id limit 10;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t2    | ref  | key1          | key1 | 5       | const |    1 | Using where |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      

      There's a workaround: mention the extended key parts explicitly:

       
      create table t3 (
        id int auto_increment,
        created_at datetime,
        key1 int,
        key2 int,
        key (key1, id),
        primary key(id,created_at)
      );
      insert into t3 select * from t1;
      

      explain 
      select * 
      from t3 ignore index(primary) 
      where key1=100  order by id limit 10;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t3    | ref  | key1          | key1 | 5       | const |    1 | Using where |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration