Hey, We are having some troubles with executing some queries that should execute fine since we have indexes present. I will try to explain as much as possible and provide you with as much details. Structure of the Table ----------------------------- CREATE TABLE `seat` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `create_date` datetime DEFAULT NULL, `game_id` bigint(20) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, `code` varchar(12) DEFAULT NULL, `outcome` tinyint(4) DEFAULT NULL, `finalscore` decimal(7,2) DEFAULT NULL, `stake` int(11) DEFAULT NULL, `winnings` decimal(10,2) DEFAULT NULL, `data` text, `lifecycle_notifications` int(11) DEFAULT NULL, `is_public` tinyint(1) DEFAULT NULL, `finalpos` int(11) DEFAULT NULL, `commission` decimal(10,2) DEFAULT NULL, `pot` decimal(10,2) DEFAULT NULL, `table_spec_id` bigint(20) DEFAULT NULL, `updated_date` datetime DEFAULT NULL, `roster_id` bigint(20) DEFAULT NULL, `entrysource` tinyint(4) DEFAULT NULL, `houseclaimed` tinyint(1) DEFAULT NULL, `bonus_earned` decimal(6,2) DEFAULT NULL, `stake_currency_type` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `game_id_idx` (`game_id`,`status`) USING HASH, KEY `code_idx` (`code`) USING HASH, KEY `seat_updated_date` (`updated_date`), KEY `ixSeatRoster` (`roster_id`), KEY `user_status_game` (`user_id`,`status`,`game_id`), KEY `table_finalpos` (`table_id`,`finalpos`), KEY `user_create_status_idx` (`user_id`,`create_date`,`status`) ) ENGINE=InnoDB Number of rows in the table --------------------------- 550 Million Query explained ------------------------ As you can see form the query explain output that optimiser knows there is an index but unfortunately it still chooses the wrong index. You can see the index defined above as game_id_idx. mysql> explain select s.id,s.game_id,s.status,s.roster_id from seat s where game_id = 16073 order by id limit 10001; +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | s | index | game_id_idx | PRIMARY | 8 | NULL | 1095256 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ 1 row in set (0.00 sec) Optimiser trace ---------------------- I enabled tracing facility and ran a trace on the query and here is the output below. You can see from the optimiser trace (rows_estimation heading) it is able to detect a range scan and estimates correct number of rows that we also expect. Secondly optimiser also detect possible indexes like this. "index": "game_id_idx", "usable": true, "key_parts": [ "game_id", "status", "id" Optimiser also rejects possibility of using primary key like this "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" But from the explain result above the optimiser at the end eventually chooses primary instead of game_id_idx index. This is the one we want to use but in the end it is reconsidering access paths for index ordering. | select s.id,s.game_id,s.status,s.roster_id from seat s where game_id = 16073 order by id limit 10001 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `s`.`id` AS `id`,`s`.`game_id` AS `game_id`,`s`.`status` AS `status`,`s`.`roster_id` AS `roster_id` from `seat` `s` where (`s`.`game_id` = 16073) order by `s`.`id` limit 10001" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`s`.`game_id` = 16073)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(16073, `s`.`game_id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(16073, `s`.`game_id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(16073, `s`.`game_id`)" } ] } }, { "table_dependencies": [ { "table": "`seat` `s`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`seat` `s`", "field": "game_id", "equals": "16073", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`seat` `s`", "range_analysis": { "table_scan": { "rows": 547042746, "cost": 1.17e8 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "game_id_idx", "usable": true, "key_parts": [ "game_id", "status", "id" ] }, { "index": "code_idx", "usable": false, "cause": "not_applicable" }, { "index": "seat_updated_date", "usable": false, "cause": "not_applicable" }, { "index": "ixSeatRoster", "usable": false, "cause": "not_applicable" }, { "index": "user_status_game", "usable": false, "cause": "not_applicable" }, { "index": "table_finalpos", "usable": false, "cause": "not_applicable" }, { "index": "user_create_status_idx", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "game_id_idx", "ranges": [ "16073 <= game_id <= 16073" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4995154, "cost": 5.99e6, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "game_id_idx", "rows": 4995154, "ranges": [ "16073 <= game_id <= 16073" ] }, "rows_for_plan": 4995154, "cost_for_plan": 5.99e6, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`seat` `s`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "game_id_idx", "rows": 5e6, "cost": 5.99e6, "chosen": true }, { "access_type": "range", "cause": "heuristic_index_cheaper", "chosen": false } ] }, "cost_for_plan": 5.99e6, "rows_for_plan": 5e6, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`s`.`game_id` = 16073)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`seat` `s`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`s`.`id`", "items": [ { "item": "`s`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`s`.`id`" } }, { "refine_plan": [ { "table": "`seat` `s`" } ] }, { "added_back_ref_condition": "((`s`.`game_id` <=> 16073))" }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`seat` `s`", "index_provides_order": true, "order_direction": "asc", "index": "PRIMARY", "plan_changed": true, "access_type": "index_scan" } } } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } | 0 | 0 | +------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+ 1 row in set (0.00 sec) I think I have done a detail analysis here and probably pinned down the errors in optimiser execution process and have shared detail output. This is a production problem. Solution -------- If we use force index in the query than optimiser is able to work with correct index, here is an example below explain select s.id,s.game_id,s.status,s.roster_id from seat s force index (game_id_idx) where game_id = 16073 order by id limit 10001; +----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+ | 1 | SIMPLE | s | ref | game_id_idx | game_id_idx | 9 | const | 4995154 | Using where; Using filesort | +----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+ 1 row in set (0.01 sec) Although force index is working but our application is huge and it is difficult to know when and where optimiser will stop using the correct index. Thanks Masood Alam