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

Ordered scans over PARTITION BY RANGE should not use priority queue

    XMLWordPrintable

Details

    • Q4/2025 Server Development

    Description

      Consider a table partitioned by date:

      CREATE TABLE t1 (
        date_created DATETIME
         user_id INT,
         ...
        INDEX idx1(user_id, date_created)
      ) PARTITION BY RANGE COLUMNS(date_created) (
       PARTITION `p_first`  VALUES LESS THAN ('2022-02-24 00:00:00'),
        ... 
       PARTITION `p_2025_02` VALUES LESS THAN ('2025-03-01 00:00:00'),
        ...
        PARTITION `p_last` VALUES LESS THAN ...
      );
      

      And a query looking to get the last row describing a given user.

      select * from t1
      where user_id=1234
      ORDER BY date_created DESC LIMIT 1
      

      The index idx1(user_id, date_created) will allow to use ref access and SQL layer will make one index lookup.

      However, ha_partition has only two algorithms:

      • unordered index scan
      • ordered index scan

      Here, ordered index scan will be used. ha_partition will read a row from each partition and put that into the priority queue, then it will get the last one.
      When there are many partitions, this will have a lot of overhead. Why can't we just read the row from partition p_last ? And after we've exhausted that, read the row from partition before the p_last and so forth?
      (This is actually similar to what "unordered index scan" code does except that code starts from partitions with smaller number and continues to partitions with larger number)

      Attachments

        Activity

          People

            ycp Yuchen Pei
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.