I populated table with test data but was not able to reproduce the problem you described. Can you please share your my.cnf file and some test data if possible. 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 | +-------------------------+--------------------------------------------------+ mysql [localhost] {root} (test) > show table status like 'suggestion'\G *************************** 1. row *************************** Name: suggestion Engine: InnoDB Version: 10 Row_format: Compact Rows: 1021746 Avg_row_length: 107 Data_length: 109723648 Max_data_length: 0 Index_length: 85671936 Data_free: 7340032 Auto_increment: NULL Create_time: 2014-09-16 03:15:39 Update_time: NULL Check_time: NULL Collation: latin1_german2_ci Checksum: NULL Create_options: Comment: $ -rw-rw---- 1 root root 41K Sep 16 03:15 suggestion.frm $ rw-rw---- 1 root root 224M Sep 16 11:23 suggestion.ibd 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 | 2 | 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 | 2 | 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 | 405222 | Using index condition; Using where | +----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+ 1 row in set (0.00 sec) ================== Query Trace Output: ================== { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `suggestion`.`obj_id` AS `obj_id`,`suggestion`.`flags` AS `flags`,`suggestion`.`data` AS `data`,`suggestion`.`type` AS `type` from `suggestion` where ((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and (`suggestion`.`flags` = 0)) order by `suggestion`.`type` desc,`suggestion`.`created` desc limit 50" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and (`suggestion`.`flags` = 0))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))" } ] } }, { "table_dependencies": [ { "table": "`suggestion`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`suggestion`", "field": "user_id", "equals": "'7723036'", "null_rejecting": false }, { "table": "`suggestion`", "field": "user_id", "equals": "'7723036'", "null_rejecting": false }, { "table": "`suggestion`", "field": "rejected", "equals": "'n'", "null_rejecting": false }, { "table": "`suggestion`", "field": "rejected", "equals": "'n'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`suggestion`", "range_analysis": { "table_scan": { "rows": 1021746, "cost": 211048 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "user_id", "type", "obj_id" ] }, { "index": "user_id", "usable": true, "key_parts": [ "user_id", "rejected", "type", "obj_id" ] }, { "index": "type", "usable": true, "key_parts": [ "type", "created", "user_id", "obj_id" ] }, { "index": "rejected", "usable": true, "key_parts": [ "rejected", "type", "created", "user_id", "obj_id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "7723036 <= user_id <= 7723036 AND group <= type <= group", "7723036 <= user_id <= 7723036 AND newbie <= type <= newbie" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2, "cost": 2.41, "chosen": true }, { "index": "user_id", "ranges": [ "7723036 <= user_id <= 7723036 AND n <= rejected <= n AND group <= type <= group", "7723036 <= user_id <= 7723036 AND n <= rejected <= n AND newbie <= type <= newbie" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2, "cost": 4.41, "chosen": false, "cause": "cost" }, { "index": "type", "ranges": [ "group <= type <= group", "newbie <= type <= newbie" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 405222, "cost": 486268, "chosen": false, "cause": "cost" }, { "index": "rejected", "ranges": [ "n <= rejected <= n AND group <= type <= group", "n <= rejected <= n AND newbie <= type <= newbie" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 358464, "cost": 430159, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "7723036 <= user_id <= 7723036 AND group <= type <= group", "7723036 <= user_id <= 7723036 AND newbie <= type <= newbie" ] }, "rows_for_plan": 2, "cost_for_plan": 2.41, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`suggestion`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 2, "cost": 2.4, "chosen": true }, { "access_type": "ref", "index": "user_id", "rows": 2, "cost": 2.4, "chosen": false }, { "access_type": "ref", "index": "rejected", "rows": 358464, "cost": 91784, "chosen": false }, { "access_type": "range", "rows": 2, "cost": 2.81, "chosen": false } ] }, "cost_for_plan": 2.4, "rows_for_plan": 2, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))", "attached_conditions_computation": [ { "access_type_changed": { "table": "`suggestion`", "index": "PRIMARY", "old_type": "ref", "new_type": "range", "cause": "uses_more_keyparts" } } ], "attached_conditions_summary": [ { "table": "`suggestion`", "attached": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`suggestion`.`type` desc,`suggestion`.`created` desc", "items": [ { "item": "`suggestion`.`type`" }, { "item": "`suggestion`.`created`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`suggestion`.`type` desc,`suggestion`.`created` desc" } }, { "refine_plan": [ { "table": "`suggestion`", "access_type": "range" } ] }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`suggestion`", "index_provides_order": false, "order_direction": "undefined", "index": "PRIMARY", "plan_changed": false } } } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`suggestion`", "field": "type" }, { "direction": "desc", "table": "`suggestion`", "field": "created" } ], "filesort_priority_queue_optimization": { "limit": 50, "rows_estimate": 5023851, "row_size": 14, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 0, "examined_rows": 0, "number_of_tmp_files": 0, "sort_buffer_size": 1122, "sort_mode": "" } } ] } } ]