I still didn't able to reproduce it. I tried to use the same percentage of data distribution among different table fields but no luck yet. It uses the optimal index i.e. PRIMARY KEY for queries regardless used with LIMIT clause or not. It is possible for you to share test data to try to reproduce ? mysql [localhost] {root} (test) > SHOW GLOBAL VARIABLES LIKE '%version%'; +-------------------------+--------------------------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------------------------+ | innodb_version | 5.6.19-rel67.0 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.19-67.0 | | version_comment | Percona Server (GPL), Release 67.0, Revision 618 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+--------------------------------------------------+ 7 rows in set (0.00 sec) mysql [localhost] {root} (test) > show create table suggestion\G *************************** 1. row *************************** Table: suggestion Create Table: CREATE TABLE `suggestion` ( `user_id` int(10) unsigned NOT NULL, `type` enum('user','group','board','newbie','dating') COLLATE latin1_german2_ci NOT NULL, `obj_id` int(10) unsigned NOT NULL, `rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n', `flags` int(10) unsigned NOT NULL DEFAULT '0', `data` varbinary(32768) NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`user_id`,`type`,`obj_id`), KEY `user_id` (`user_id`,`rejected`), KEY `type` (`type`,`created`), KEY `rejected` (`rejected`,`type`,`created`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci mysql [localhost] {root} (test) > show table status like 'suggestion'\G *************************** 1. row *************************** Name: suggestion Engine: InnoDB Version: 10 Row_format: Compact Rows: 1836214 Avg_row_length: 75 Data_length: 139132928 Max_data_length: 0 Index_length: 102465536 Data_free: 0 Auto_increment: NULL Create_time: 2014-10-31 09:10:04 Update_time: NULL Check_time: NULL Collation: latin1_german2_ci Checksum: NULL Create_options: Comment: -rw-rw---- 1 root root 41K Oct 31 09:08 suggestion.frm -rw-rw---- 1 root root 232M Oct 31 09:13 suggestion.ibd mysql [localhost] {root} (test) > SELECT type, COUNT(type) FROM suggestion GROUP BY type; +--------+-------------+ | type | COUNT(type) | +--------+-------------+ | user | 183817 | | group | 184566 | | board | 184760 | | newbie | 183507 | | dating | 1107810 | +--------+-------------+ 5 rows in set (0.55 sec) mysql [localhost] {root} (test) > SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected; +----------+-----------------+ | rejected | COUNT(rejected) | +----------+-----------------+ | y | 183675 | | n | 1660785 | +----------+-----------------+ 2 rows in set (0.59 sec) mysql [localhost] {root} (test) > SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags; +-------+--------------+ | flags | COUNT(flags) | +-------+--------------+ | 0 | 1244460 | | 1 | 500000 | | 2 | 55539 | | 3 | 44461 | +-------+--------------+ 4 rows in set (3.11 sec) mysql [localhost] {root} (test) > select count(*) from suggestion where user_id=7723036; +----------+ | count(*) | +----------+ | 150 | +----------+ 1 row in set (0.05 sec) mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50; +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort | +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC; +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort | +----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion FORCE INDEX(type) WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50; +----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+ | 1 | SIMPLE | suggestion | range | type | type | 1 | NULL | 706824 | Using index condition; Using where | +----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+ 1 row in set (0.00 sec)