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

select count(*) on hash partitions does a sorted index scan

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0.12
    • 11.0(EOL)
    • None
    • None
    • ubuntu

    Description

      select count(*) on hash partitions does a sorted index scan. the sort is unnecessary since the count operator is commutative. this problem also exists in MySQL 5.5 but is fixed in MySQL 5.6.19.

      Attachments

        Issue Links

          Activity

            Hi Rich,

            Could you please provide an example – CREATE TABLE and the plan that you see (or any other indications that you use to detect the sorted index scan)?

            elenst Elena Stepanova added a comment - Hi Rich, Could you please provide an example – CREATE TABLE and the plan that you see (or any other indications that you use to detect the sorted index scan)?
            prohaska7 Rich Prohaska added a comment - - edited

            run mysqld with gdb and put a breakpoint at ha_partition::index_init
            create table t (id bigint not null auto_increment primary key) partition by hash(id) partitions 2;
            insert some rows into t.
            select count(*) from t
            the breakpoint hits and and the sorted parameter is true
            ha_partition::index_init (this=0x7ffd81caa088, inx=0, sorted=true)
            this causes the partition storage engine to sort the result set.
            for tokudb 7.1.8-rc5, we think that this is a 2x slowdown.
            in contrast, mysql 5.6 uses an unsorted indexing operation for this query.

            prohaska7 Rich Prohaska added a comment - - edited run mysqld with gdb and put a breakpoint at ha_partition::index_init create table t (id bigint not null auto_increment primary key) partition by hash(id) partitions 2; insert some rows into t. select count(*) from t the breakpoint hits and and the sorted parameter is true ha_partition::index_init (this=0x7ffd81caa088, inx=0, sorted=true) this causes the partition storage engine to sort the result set. for tokudb 7.1.8-rc5, we think that this is a 2x slowdown. in contrast, mysql 5.6 uses an unsorted indexing operation for this query.
            elenst Elena Stepanova added a comment - - edited

            Thanks, yes, I'm getting it this way also.
            Leaving to psergei to decide if it's a bug and where it can/should be fixed, 10.0 in 'Fix Version' is tentative and can be changed both ways if necessary.

            elenst Elena Stepanova added a comment - - edited Thanks, yes, I'm getting it this way also. Leaving to psergei to decide if it's a bug and where it can/should be fixed, 10.0 in 'Fix Version' is tentative and can be changed both ways if necessary.

            People

              psergei Sergei Petrunia
              prohaska7 Rich Prohaska
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.