Optimizer doesn't choose best execution plan when composite key is used.

MariaDB [test]> select version();

+---------------------+

| version()           |

+---------------------+

| 10.0.11-MariaDB-log |

+---------------------+

1 row in set (0.00 sec)

MariaDB [test]> show variables like 'optimizer_switch'\G

*************************** 1. row ***************************

Variable_name: optimizer_switch

        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=off

1 row in set (0.00 sec)

MariaDB [test]> show create table tb_test\G

*************************** 1. row ***************************

       Table: tb_test

Create Table: CREATE TABLE `tb_test` (

  `pk1` int(11) NOT NULL,

  `pk2` int(11) NOT NULL,

  `fd1` int(11) NOT NULL,

  `fd2` bigint(20) NOT NULL DEFAULT '0',

  `fd3` bigint(20) NOT NULL DEFAULT '0',

  `fd4` datetime NOT NULL,

  `fd5` bigint(20) DEFAULT NULL,

  `fd6` varchar(64) DEFAULT NULL,

  `fd7` text,

  `fd8` varchar(64) DEFAULT NULL,

  `fd9` tinyint(1) NOT NULL DEFAULT '1',

  `fd10` bigint(20) NOT NULL DEFAULT '0',

  `fd11` tinyint(1) DEFAULT NULL,

  `fd12` tinyint(1) DEFAULT NULL,

  PRIMARY KEY (`pk1`,`pk2`),

  UNIQUE KEY `ux_pk1_fd5` (`pk1`,`fd5`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

/*!50100 PARTITION BY KEY (pk1)

PARTITIONS 5 */

1 row in set (0.00 sec)

MariaDB [test]> alter table tb_test engine=innodb;

Query OK, 0 rows affected (7 min 3.85 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show table status like 'tb_test'\G

*************************** 1. row ***************************

           Name: tb_test

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 8366199

 Avg_row_length: 96

    Data_length: 806895616

Max_data_length: 0

   Index_length: 195002368

      Data_free: 0

 Auto_increment: NULL

    Create_time: NULL

    Update_time: NULL

     Check_time: NULL

      Collation: utf8mb4_general_ci

       Checksum: NULL

 Create_options: partitioned

        Comment:

1 row in set (0.02 sec)

MariaDB [test]> show index from tb_test;

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| tb_test |          0 | PRIMARY    |            1 | pk1         | A         |     4183099 |     NULL | NULL   |      | BTREE      |         |               |

| tb_test |          0 | PRIMARY    |            2 | pk2         | A         |     8366199 |     NULL | NULL   |      | BTREE      |         |               |

| tb_test |          0 | ux_pk1_fd5 |            1 | pk1         | A         |     8366199 |     NULL | NULL   |      | BTREE      |         |               |

| tb_test |          0 | ux_pk1_fd5 |            2 | fd5         | A         |     8366199 |     NULL | NULL   | YES  | BTREE      |         |               |

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM tb_test USE INDEX(ux_pk1_fd5) WHERE pk1=8287001 AND fd5<91866952691281442 ORDER BY fd5 DESC LIMIT 201,1;

+------+-------------+---------+-------+---------------+------------+---------+------+-------+-------------+

| id   | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows  | Extra       |

+------+-------------+---------+-------+---------------+------------+---------+------+-------+-------------+

|    1 | SIMPLE      | tb_test | range | ux_pk1_fd5    | ux_pk1_fd5 | 13      | NULL | 14590 | Using where |

+------+-------------+---------+-------+---------------+------------+---------+------+-------+-------------+

1 row in set (0.01 sec)

MariaDB [test]> EXPLAIN SELECT * FROM tb_test WHERE pk1=8287001 AND fd5<91866952691281442 ORDER BY fd5 DESC LIMIT 201,1;

+------+-------------+---------+------+--------------------+------------+---------+-------+-------+-------------+

| id   | select_type | table   | type | possible_keys      | key        | key_len | ref   | rows  | Extra       |

+------+-------------+---------+------+--------------------+------------+---------+-------+-------+-------------+

|    1 | SIMPLE      | tb_test | ref  | PRIMARY,ux_pk1_fd5 | ux_pk1_fd5 | 4       | const | 65108 | Using where |

+------+-------------+---------+------+--------------------+------------+---------+-------+-------+-------------+

1 row in set (0.01 sec)

MariaDB [test]> show status like 'Handler_read_prev';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Handler_read_prev | 0     |

+-------------------+-------+

1 row in set (0.02 sec)

MariaDB [test]> SELECT * FROM tb_test WHERE pk1=8287001 AND fd5<91866952691281442 ORDER BY fd5 DESC LIMIT 201,1;

...

1 row in set (0.27 sec)

MariaDB [test]> show status like 'Handler_read_prev';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Handler_read_prev | 28201 |

+-------------------+-------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM tb_test USE INDEX(ux_pk1_fd5) WHERE pk1=8287001 AND fd5<91866952691281442 ORDER BY fd5 DESC LIMIT 201,1;

...

1 row in set (0.01 sec)

MariaDB [test]> show status like 'Handler_read_prev';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Handler_read_prev | 28402 |

+-------------------+-------+

1 row in set (0.00 sec)