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

explain index choice

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • N/A
    • Optimizer
    • None

    Description

      Hi guys, i openned this MDEV from MDEV-7125 problem...

      +------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+
      | id   | select_type | table | type  | possible_keys                       | key      | key_len | ref                             | rows | filtered | Extra                                                        |
      +------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+
      |    1 | SIMPLE      | b     | index | NULL                                | id       | 16      | NULL                            |    2 |   100.00 | Using index                                                  |
      |    1 | SIMPLE      | a     | ref   | item,transferencias,rendimento,giro | item     | 6       | const,test.b.plano_conta_id_red | 3318 |   100.00 | Using where                                                  |
      |    1 | SIMPLE      | c     | index | NULL                                | NewIndex | 16      | NULL                            |    2 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
      +------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

      what's the idea of this mdev?
      the second row, table 'a', have many possible_keys, but we don't know why optimizer selected the item index
      what i do is : explain many times, forcing each index each explain... for example
      1) force index (item)
      2) force index (transferencias)
      3) force index (rendimento)
      4) force index (giro)
      to get each explain
      i don't know if optimizer have this values, but probably yes since it select the best index before returning explain results

      my idea is include one new column with a dynamic output (json or dynamic column)
      this will include all possible values from each index, for example:

      {
        'item':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
        'transferencias':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
        'rendimento':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
        'giro':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'}
      }

      i don't know the complexity of this mdev but it's interesting to better know what optimizer do

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              rspadim roberto spadim
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.