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

Analyze show ref(const,const) when we are using index scan

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Optimizer
    • None

    Description

      The dataset used

      create table t0 (a int);
      INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);
       
      CREATE TABLE t1 (
      a int(11) DEFAULT NULL,
      b int(11) DEFAULT NULL,
      d int(11) DEFAULT NULL,
      KEY a_d (a,d),
      KEY a_b (a,b)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
      

      MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | index | a_d,a_b       | a_b | 10      | const,const | 10266 | 10000.00 |     1.95 |       2.00 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      

      ref should be NULL in the ouput as we are using index scan , ref is set when we use ref-access

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.