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

Backward index scan is not reflected in EXPLAIN output

    XMLWordPrintable

    Details

      Description

      MySQL's version of desc indexes implementation includes the indication of the backward scan in EXPLAIN:

      MySQL 8.0.23

      EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b DESC;
      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	NULL	index	NULL	a_desc_b_asc	10	NULL	2	100.00	Backward index scan; Using index
      Warnings:
      Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` desc
      FLUSH STATUS;
      SELECT * FROM t1 ORDER BY a ASC, b DESC;
      a	b
      1	2
      2	1
      SHOW STATUS LIKE 'Handler_read_prev';
      Variable_name	Value
      Handler_read_prev	2
      

      MariaDB's currently doesn't:

      preview-10.8-MDEV-13756-desc-indexes d6fa6e0a

      EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b DESC;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	index	NULL	a_desc_b_asc	10	NULL	2	100.00	Using index
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` desc
      FLUSH STATUS;
      SELECT * FROM t1 ORDER BY a ASC, b DESC;
      a	b
      1	2
      2	1
      SHOW STATUS LIKE 'Handler_read_prev';
      Variable_name	Value
      Handler_read_prev	2
      

      As discussed elsewhere, it apparently should.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.