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

index_merge sort_union not work

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.19, 10.5.10
    • Fix Version/s: 10.4, 10.5
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Performance regression , "index_merge" optimization not used

      > show create table mytable
      CREATE TABLE `mytable` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `show` enum('Y','N') CHARACTER SET utf8 NOT NULL,
        `city` int(10) unsigned NOT NULL,
        `info` tinyint(1) NOT NULL DEFAULT 0,
        `uses` tinyint(4) NOT NULL,
        `subd` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `subd` (`subd`),
        KEY `info` (`info`),
        KEY `show` (`show`)
      ) ENGINE=InnoDB AUTO_INCREMENT=5454915 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      

      optimization ok

      mariadb 10.3.28

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      | id   | select_type | table   | type        | possible_keys  | key       | key_len | ref  | rows | Extra                                    |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      |    1 | SIMPLE      | mytable | index_merge | subd,info,show | info,subd | 1,384   | NULL |   59 | Using sort_union(info,subd); Using where |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      

      mariadb 10.3.29

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      | id   | select_type | table   | type        | possible_keys  | key       | key_len | ref  | rows | Extra                                    |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      |    1 | SIMPLE      | mytable | index_merge | subd,info,show | info,subd | 1,384   | NULL |   59 | Using sort_union(info,subd); Using where |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      1 row in set (0.002 sec)
      

      mariadb 10.4.18

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1)); 
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      | id   | select_type | table   | type        | possible_keys  | key       | key_len | ref  | rows | Extra                                    |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      |    1 | SIMPLE      | mytable | index_merge | subd,info,show | info,subd | 1,384   | NULL | 59   | Using sort_union(info,subd); Using where |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      

      mariadb 10.5.9

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      | id   | select_type | table   | type        | possible_keys  | key       | key_len | ref  | rows | Extra                                    |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      |    1 | SIMPLE      | mytable | index_merge | subd,info,show | info,subd | 1,384   | NULL | 59   | Using sort_union(info,subd); Using where |
      +------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
      

      mysql 5.7.33

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      | id | select_type | table   | partitions | type        | possible_keys  | key       | key_len | ref  | rows | filtered | Extra                                    |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      |  1 | SIMPLE      | mytable | NULL       | index_merge | subd,info,show | info,subd | 1,384   | NULL |   59 |    50.00 | Using sort_union(info,subd); Using where |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      

      mysql 5.7.34

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      | id | select_type | table   | partitions | type        | possible_keys  | key       | key_len | ref  | rows | filtered | Extra                                    |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      |  1 | SIMPLE      | mytable | NULL       | index_merge | subd,info,show | info,subd | 1,384   | NULL |   59 |    50.00 | Using sort_union(info,subd); Using where |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      

      mysql 8.0.25

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      | id | select_type | table   | partitions | type        | possible_keys  | key       | key_len | ref  | rows | filtered | Extra                                    |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      |  1 | SIMPLE      | mytable | NULL       | index_merge | subd,info,show | info,subd | 1,384   | NULL |   59 |    50.00 | Using sort_union(info,subd); Using where |
      +----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
      

      optimization miss

      mariadb 10.4.19

      > explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
      | id   | select_type | table   | type | possible_keys  | key  | key_len | ref   | rows    | Extra                              |
      +------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
      |    1 | SIMPLE      | mytable | ref  | subd,info,show | show | 1       | const | 1771114 | Using index condition; Using where |
      +------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
      

      mariadb 10.5.10

      explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
      +------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
      | id   | select_type | table   | type | possible_keys  | key  | key_len | ref  | rows    | Extra       |
      +------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
      |    1 | SIMPLE      | mytable | ALL  | subd,info,show | NULL | NULL    | NULL | 3518748 | Using where |
      +------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            kpilnik kpilnik
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration