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

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

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.12
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None
        • Environment:
          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                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)?

                Show
                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)?
                Hide
                Permalink
                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.

                Show
                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.
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Thanks, yes, I'm getting it this way also.
                Leaving to Sergei Petrunia 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.

                Show
                elenst Elena Stepanova added a comment - Thanks, yes, I'm getting it this way also. Leaving to Sergei Petrunia 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

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    prohaska7 Rich Prohaska
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated: