Comment 6 for bug 1362212

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)