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

It seems like OPTIMIZER take into account the order of indexes in the table.

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5.39, 10.0.11
    • Fix Version/s: 10.1.1
    • Component/s: None
    • Environment:
      Linux matt001 2.6.18-308.el5 #1 SMP Tue Feb 21 20:06:06 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

      Description

      I created two test table. The only difference between them is the order of secondary index.
      And I explained same query on two table after insert same test data in two tables.
      But query execution plan is different.
      << First see the below test case >>

      Here's the problem.
      If "ix_fd1_fd2" index is UNIQUE, optimizer never use "ix_fd_fdpk" index in this case. Becuase UNIQUE index will be positioned before NORMAL secondary index like below table, even though it is added later.

      CREATE TABLE `tb_test1` (
        ...
        PRIMARY KEY (`fd_pk`),
        UNIQUE KEY `ux_fd1_fd2` (`fd1`,`fd2`),
        KEY `ix_fd_fdpk` (`fd1`,`fd_pk`),
        KEY `ix_fd1_fd2` (`fd1`,`fd2`),
      );
      

      How can I make optimizer use ix_fd1_fdpk to avoid filesort operation (Without index hint ^^) ?
      And is this expected ?

      -- TEST CASE ------------------------
      // Prepare some test data and table
      MariaDB [test]> INSERT INTO tb_test1 SELECT NULL, ORDINAL_POSITION, IFNULL(CHARACTER_MAXIMUM_LENGTH, ROUND(RAND()*10000)), NOW(), 'dummy' FROM information_schema.COLUMNS;
      Query OK, 1886 rows affected (0.26 sec)
      Records: 1886  Duplicates: 0  Warnings: 0
      
      MariaDB [test]> INSERT INTO tb_test2 SELECT NULL, ORDINAL_POSITION, IFNULL(CHARACTER_MAXIMUM_LENGTH, ROUND(RAND()*10000)), NOW(), 'dummy' FROM information_schema.COLUMNS;
      Query OK, 1886 rows affected (0.23 sec)
      Records: 1886  Duplicates: 0  Warnings: 0
      
      MariaDB [test]> alter table tb_test1 engine=innodb;
      Query OK, 0 rows affected (0.79 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      MariaDB [test]> alter table tb_test2 engine=innodb;
      Query OK, 0 rows affected (0.45 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      MariaDB [test]> show table status like 'tb_test1'\G
      *************************** 1. row ***************************
                 Name: tb_test1
               Engine: InnoDB
              Version: 10
           Row_format: Compact
                 Rows: 1886
       Avg_row_length: 78
          Data_length: 147456
      Max_data_length: 0
         Index_length: 147456
            Data_free: 0
       Auto_increment: 2048
          Create_time: 2014-06-25 08:29:11
          Update_time: NULL
           Check_time: NULL
            Collation: utf8_general_ci
             Checksum: NULL
       Create_options: 
              Comment: 
      1 row in set (0.00 sec)
      
      MariaDB [test]> show table status like 'tb_test2'\G
      *************************** 1. row ***************************
                 Name: tb_test2
               Engine: InnoDB
              Version: 10
           Row_format: Compact
                 Rows: 1886
       Avg_row_length: 78
          Data_length: 147456
      Max_data_length: 0
         Index_length: 147456
            Data_free: 0
       Auto_increment: 2048
          Create_time: 2014-06-25 08:29:13
          Update_time: NULL
           Check_time: NULL
            Collation: utf8_general_ci
             Checksum: NULL
       Create_options: 
              Comment: 
      1 row in set (0.00 sec)
      
      MariaDB [test]> show index from tb_test1;
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      | Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality |..
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      | tb_test1 |          0 | PRIMARY    |            1 | fd_pk       | A         |        1886 |..
      | tb_test1 |          1 | ix_fd_fdpk |            1 | fd1         | A         |         157 |..
      | tb_test1 |          1 | ix_fd_fdpk |            2 | fd_pk       | A         |        1886 |..
      | tb_test1 |          1 | ix_fd1_fd2 |            1 | fd1         | A         |         157 |..
      | tb_test1 |          1 | ix_fd1_fd2 |            2 | fd2         | A         |        1886 |..
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      5 rows in set (0.00 sec)
      MariaDB [test]> show index from tb_test2;
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      | Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality |..
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      | tb_test2 |          0 | PRIMARY    |            1 | fd_pk       | A         |        1886 |..
      | tb_test2 |          1 | ix_fd1_fd2 |            1 | fd1         | A         |         157 |..
      | tb_test2 |          1 | ix_fd1_fd2 |            2 | fd2         | A         |        1886 |..
      | tb_test2 |          1 | ix_fd_fdpk |            1 | fd1         | A         |         157 |..
      | tb_test2 |          1 | ix_fd_fdpk |            2 | fd_pk       | A         |        1886 |..
      +----------+------------+------------+--------------+-------------+-----------+-------------+..
      5 rows in set (0.00 sec)
      
      MariaDB [test]> explain select * from tb_test1 where fd1=1 order by fd_pk limit 1000;
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+
      | id   | select_type | table    | type | possible_keys         | key        | key_len | ref   | rows | Extra       |
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+
      |    1 | SIMPLE      | tb_test1 | ref  | ix_fd_fdpk,ix_fd1_fd2 | ix_fd_fdpk | 8       | const |  172 | Using where |
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+
      1 row in set (0.00 sec)
      
      MariaDB [test]> explain select * from tb_test2 where fd1=1 order by fd_pk limit 1000;
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+
      | id   | select_type | table    | type | possible_keys         | key        | key_len | ref   | rows | Extra                       |
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+
      |    1 | SIMPLE      | tb_test2 | ref  | ix_fd1_fd2,ix_fd_fdpk | ix_fd1_fd2 | 8       | const |  172 | Using where; Using filesort |
      +------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+
      1 row in set (0.00 sec)
      

      ==> The last query doesn't need filesort operation when they use ix_fd1_fdpk index. But not..

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                Matt74 Matt74
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: