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

MariaDB doesn't choose DESC index for ORDER BY DESC when MySQL does

Details

    Description

      MySQL's WL#1074 mentions a limitation:

      when user has two indexes, one ASC and another DESC over the same column, optimizer won't necessary will be able to pick the right index

      I suppose it applies to MariaDB as well, even though MDEV-13756 doesn't say so.

      However, there might still be room for improvement. At least in some cases MySQL is able to pick up the DESC index out of two, while MariaDB isn't:

      # Remove the include if you run the test on MySQL 8.0
      		 
      --source include/have_innodb.inc
      		 
       
      		 
      create table t (a int, key aa(a), key ad(a desc)) engine=InnoDB;
      		 
      insert into t values (1),(5),(2),(8),(4),(6),(7),(9),(3);
      		 
       
      		 
      explain select * from t order by a desc;
      		 
      flush status;
      		 
      select * from t order by a desc;
      		 
      show status like 'Handler_read%';
      		 
       
      		 
      # Cleanup
      		 
      drop table t;

      MariaDB preview-10.8-MDEV-13756-desc-indexes 43444ff5

      		 
      explain select * from t order by a desc;
      		 
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      		 
      1	SIMPLE	t	index	NULL	aa	5	NULL	9	Using index
      		 
       
      		 
      Handler_read_first	0
      		 
      Handler_read_key	0
      		 
      Handler_read_last	1
      		 
      Handler_read_next	0
      		 
      Handler_read_prev	9
      		 
      Handler_read_retry	0
      		 
      Handler_read_rnd	0
      		 
      Handler_read_rnd_deleted	0
      		 
      Handler_read_rnd_next	0

      MySQL 8.0.23

      		 
      explain select * from t order by a desc;
      		 
      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      		 
      1	SIMPLE	t	NULL	index	NULL	ad	5	NULL	10	100.00	Using index
      		 
       
      		 
      Handler_read_first	1
      		 
      Handler_read_key	1
      		 
      Handler_read_last	0
      		 
      Handler_read_next	9
      		 
      Handler_read_prev	0
      		 
      Handler_read_rnd	0
      		 
      Handler_read_rnd_next	0

      ANALYZE/statistics collection doesn't change the outcome.

      Same happens on MariaDB with MyISAM/Aria (ASC index is chosen), but it's not comparable to MySQL because MySQL doesn't support DESC indexes on MyISAM tables.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30845 filesort still select when desc/asc index matches ORDER BY

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Assignee Sergei Golubchik [ serg ]
            elenst Elena Stepanova made changes -
            Description MySQL's [WL#1074|https://dev.mysql.com/worklog/task/?id=1074] mentions a limitation:
            {quote}
            when user has two indexes, one ASC and another DESC over the same column, optimizer won't necessary will be able to pick the right index
            {quote}
            I suppose it applies to MariaDB as well, even though MDEV-13756 doesn't say so.

            However, there might still be room for improvement. At least in some cases MySQL is able to pick up the DESC index out of two, while MariaDB isn't:

            {code:sql}
            # Remove the include if you run the test on MySQL 8.0
            --source include/have_innodb.inc

            create table t (a int, key aa(a), key ad(a desc)) engine=InnoDB;
            insert into t values (1),(5),(2),(8),(4),(6),(7),(9),(3);

            explain select * from t order by a desc;
            flush status;
            select * from t order by a desc;
            show status like 'Handler_read%';

            # Cleanup
            drop table t;
            {code}

            {code:sql|title=MariaDB preview-10.8-MDEV-13756-desc-indexes 43444ff5}
            explain select * from t order by a desc;
            id select_type table type possible_keys key key_len ref rows Extra
            1 SIMPLE t index NULL aa 5 NULL 9 Using index

            Handler_read_first 0
            Handler_read_key 0
            Handler_read_last 1
            Handler_read_next 0
            Handler_read_prev 9
            Handler_read_retry 0
            Handler_read_rnd 0
            Handler_read_rnd_deleted 0
            Handler_read_rnd_next 0
            {code}

            {code:sql|title=MySQL 8.0.23}
            explain select * from t order by a desc;
            id select_type table partitions type possible_keys key key_len ref rows filtered Extra
            1 SIMPLE t NULL index NULL ad 5 NULL 10 100.00 Using index

            Handler_read_first 1
            Handler_read_key 1
            Handler_read_last 0
            Handler_read_next 9
            Handler_read_prev 0
            Handler_read_rnd 0
            Handler_read_rnd_next 0
            {code}

            ANALYZE/statistics collection doesn't change the outcome.

            Same happens on MariaDB with MyISAM/Aria, but it's not comparable to MySQL because MySQL doesn't support DESC indexes on MyISAM tables.             		MySQL's [WL#1074|https://dev.mysql.com/worklog/task/?id=1074] mentions a limitation:
            {quote}
            when user has two indexes, one ASC and another DESC over the same column, optimizer won't necessary will be able to pick the right index
            {quote}
            I suppose it applies to MariaDB as well, even though MDEV-13756 doesn't say so.

            However, there might still be room for improvement. At least in some cases MySQL is able to pick up the DESC index out of two, while MariaDB isn't:

            {code:sql}
            # Remove the include if you run the test on MySQL 8.0
            --source include/have_innodb.inc

            create table t (a int, key aa(a), key ad(a desc)) engine=InnoDB;
            insert into t values (1),(5),(2),(8),(4),(6),(7),(9),(3);

            explain select * from t order by a desc;
            flush status;
            select * from t order by a desc;
            show status like 'Handler_read%';

            # Cleanup
            drop table t;
            {code}

            {code:sql|title=MariaDB preview-10.8-MDEV-13756-desc-indexes 43444ff5}
            explain select * from t order by a desc;
            id select_type table type possible_keys key key_len ref rows Extra
            1 SIMPLE t index NULL aa 5 NULL 9 Using index

            Handler_read_first 0
            Handler_read_key 0
            Handler_read_last 1
            Handler_read_next 0
            Handler_read_prev 9
            Handler_read_retry 0
            Handler_read_rnd 0
            Handler_read_rnd_deleted 0
            Handler_read_rnd_next 0
            {code}

            {code:sql|title=MySQL 8.0.23}
            explain select * from t order by a desc;
            id select_type table partitions type possible_keys key key_len ref rows filtered Extra
            1 SIMPLE t NULL index NULL ad 5 NULL 10 100.00 Using index

            Handler_read_first 1
            Handler_read_key 1
            Handler_read_last 0
            Handler_read_next 9
            Handler_read_prev 0
            Handler_read_rnd 0
            Handler_read_rnd_next 0
            {code}

            ANALYZE/statistics collection doesn't change the outcome.

            Same happens on MariaDB with MyISAM/Aria (ASC index is chosen), but it's not comparable to MySQL because MySQL doesn't support DESC indexes on MyISAM tables.
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.12 [ 28320 ]
            Fix Version/s 10.8 [ 26121 ]
            psergei Sergei Petrunia made changes -
            Component/s Optimizer [ 10200 ]
            Component/s Server [ 13907 ]
            psergei Sergei Petrunia made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            danblack Daniel Black made changes -
            psergei Sergei Petrunia made changes -
            Labels optimizer-feature
            ycp Yuchen Pei made changes -
            Assignee Sergei Petrunia [ psergey ] Yuchen Pei [ JIRAUSER52627 ]

            People

              ycp Yuchen Pei
              elenst Elena Stepanova
              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.