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

Optimizer trace: make best_access_path.chosen_access_method show the which index is used

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: 10.9
    • Component/s: Optimizer
    • Labels:
      None

      Description

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int primary key);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      create table t10 (a int, b int, c int, key(a), key(b));
      insert into t10 select A.a, A.a, A.a from one_k A, ten B;
      

      set optimizer_trace=1;
      explain select * from ten,t10 where t10.a=ten.a and t10.b=ten.a;
      +------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref        | rows | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
      |    1 | SIMPLE      | ten   | index | PRIMARY       | PRIMARY | 4       | NULL       | 10   | Using index |
      |    1 | SIMPLE      | t10   | ref   | a,b           | a       | 5       | test.ten.a | 1    | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
      select * from information_schema.optimizer_trace\G
      

          "plan_prefix": ["ten"],
          "table": "t10",
          "best_access_path": {
            "considered_access_paths": [
              {
                "access_type": "ref",
                "index": "a",
                "used_range_estimates": false,
                "cause": "not available",
                "rows": 1,
                "cost": 20.00134269,
                "chosen": true
              },
              {
                "access_type": "ref",
                "index": "b",
                "used_range_estimates": false,
                "cause": "not available",
                "rows": 1,
                "cost": 20.00134269,
                "chosen": false,
                "cause": "cost"
              },
              {
                "type": "scan",
                "chosen": false,
                "cause": "cost"
              }
            ],
      

          "chosen_access_method": {
            "type": "ref",
            "records": 1,
            "cost": 20.00134269,
            "uses_join_buffering": false
          }
        },
      

      Now, ask a question - which index was chosen? chosen_access_method doesn't show it.

      If one looks carefully at the considered options, they have "chosen": false|true, but this is not obvious at the first glance.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            psergei Sergei Petrunia
            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.