Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      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

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                rspadim roberto spadim
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: