We're updating the issue view to help you get more done.Learn more

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

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..

Status