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

index_merge intersect(index_that_covers_all_columns, index_that_covers_some)

    Details

      Description

      From jkavalik on #maria:

      index_merge/intersection plan is generated even when one index covers all columns:

      explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket WHERE user_id=286 AND vyplacen=1 GROUP BY user_id;
      +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
      | id   | select_type | table  | type        | possible_keys                     | key                      | key_len | ref  | rows  | Extra                                                  |
      +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
      |    1 | SIMPLE      | ticket | index_merge | FK_ticket_1,vyplacen,realWinNetto | FK_ticket_1,realWinNetto | 4,6     | NULL | 44496 | Using intersect(FK_ticket_1,realWinNetto); Using where |
      +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+

      show index from ticket; (15 non-relevant indexes removed)
      +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table  | Non_unique | Key_name                  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | ticket |          0 | PRIMARY                   |            1 | ticket_id                 | A         |     2208874 |     NULL | NULL   |      | BTREE      |         |               |
      | ticket |          1 | FK_ticket_1               |            1 | user_id                   | A         |       26937 |     NULL | NULL   |      | BTREE      |         |               |
      | ticket |          1 | vyplacen                  |            1 | vyplacen                  | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
      | ticket |          1 | realWinNetto              |            1 | user_id                   | A         |        8398 |     NULL | NULL   |      | BTREE      |         |               |
      | ticket |          1 | realWinNetto              |            2 | vyplacen                  | A         |       10774 |     NULL | NULL   |      | BTREE      |         |               |
      +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket FORCE INDEX(realWinNetto) WHERE user_id=286 AND vyplacen=1;
      +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+
      | id   | select_type | table  | type | possible_keys | key          | key_len | ref         | rows  | Extra |
      +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+
      |    1 | SIMPLE      | ticket | ref  | realWinNetto  | realWinNetto | 6       | const,const | 52762 |       |
      +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated: