mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: SELECT /*!40001 SQL_NO_CACHE */ `testtype`, `testtype`, `gid`, `testtype`, `gid`, `gid2` FROM `shard01`.`edgedata` FORCE INDEX(`PRIMARY`) WHERE ((`testtype` > X'2122919859220CC00000000000000001') OR (`testtype` = X'2122919859220CC00000000000000001' AND `gid` > X'744C9F6116DD92A30000000000000000') OR (`testtype` =X'2122919859220CC00000000000000001' AND `gid` = X'744C9F6116DD92A30000000000000000' AND `gid2` > X'744C9F6116DD92A30000000000000000')) ORDER BY `testtype`, `gid`, `gid2` LIMIT 1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_no_cache `edgedata`.`testtype` AS `testtype`,`edgedata`.`testtype` AS `testtype`,`edgedata`.`gid` AS `gid`,`edgedata`.`testtype` AS `testtype`,`edgedata`.`gid` AS `gid`,`edgedata`.`gid2` AS `gid2` from `edgedata` FORCE INDEX (PRIMARY) where ((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000)) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` = 0x744c9f6116dd92a30000000000000000) and (`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000))) order by `edgedata`.`testtype`,`edgedata`.`gid`,`edgedata`.`gid2` limit 1" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000)) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` = 0x744c9f6116dd92a30000000000000000) and (`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`)) or ((`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`) and multiple equal(0x744c9f6116dd92a30000000000000000, `edgedata`.`gid`)))" }, { "transformation": "constant_propagation", "resulting_condition": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`)) or ((`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`) and multiple equal(0x744c9f6116dd92a30000000000000000, `edgedata`.`gid`)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`)) or ((`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000) and multiple equal(0x2122919859220cc00000000000000001, `edgedata`.`testtype`) and multiple equal(0x744c9f6116dd92a30000000000000000, `edgedata`.`gid`)))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`edgedata` FORCE INDEX (PRIMARY)", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`edgedata` FORCE INDEX (PRIMARY)", "range_analysis": { "table_scan": { "rows": 9077248562, "cost": 2e308 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "testtype", "gid", "gid2" ] }, { "index": "edgedata_key_v3", "usable": false, "cause": "not_applicable" } ], "best_covering_index_scan": { "index": "PRIMARY", "cost": 1.93e9, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "0x2122919859220cc00000000000000001 <= testtype <= 0x2122919859220cc00000000000000001 AND 0x744c9f6116dd92a30000000000000000 <= gid <= 0x744c9f6116dd92a30000000000000000 AND 0x1000744c9f6116dd92a30000000000000000 < gid2", "0x2122919859220cc00000000000000001 <= testtype <= 0x2122919859220cc00000000000000001 AND 0x744c9f6116dd92a30000000000000000 < gid", "0x2122919859220cc00000000000000001 < testtype" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 9493095212, "cost": 1.92e9, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 9493095212, "ranges": [ "0x2122919859220cc00000000000000001 <= testtype <= 0x2122919859220cc00000000000000001 AND 0x744c9f6116dd92a30000000000000000 <= gid <= 0x744c9f6116dd92a30000000000000000 AND 0x1000744c9f6116dd92a30000000000000000 < gid2", "0x2122919859220cc00000000000000001 <= testtype <= 0x2122919859220cc00000000000000001 AND 0x744c9f6116dd92a30000000000000000 < gid", "0x2122919859220cc00000000000000001 < testtype" ] }, "rows_for_plan": 9493095212, "cost_for_plan": 1.92e9, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`edgedata` FORCE INDEX (PRIMARY)", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 9493095212, "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "resulting_rows": 9.49e9, "cost": 3.82e9, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 9.49e9, "cost_for_plan": 3.82e9, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000)) or ((`edgedata`.`gid` = 0x744c9f6116dd92a30000000000000000) and (`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000)))", "attached_conditions_computation": [ { "table": "`edgedata` FORCE INDEX (PRIMARY)", "rechecking_index_usage": { "recheck_reason": "low_limit", "limit": 1, "row_estimate": 9.49e9 } } ], "attached_conditions_summary": [ { "table": "`edgedata` FORCE INDEX (PRIMARY)", "attached": "((`edgedata`.`testtype` > 0x2122919859220cc00000000000000001) or ((`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid` > 0x744c9f6116dd92a30000000000000000)) or ((`edgedata`.`gid` = 0x744c9f6116dd92a30000000000000000) and (`edgedata`.`testtype` = 0x2122919859220cc00000000000000001) and (`edgedata`.`gid2` > 0x744c9f6116dd92a30000000000000000)))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`edgedata`.`testtype`,`edgedata`.`gid`,`edgedata`.`gid2`", "items": [ { "item": "`edgedata`.`testtype`" }, { "item": "`edgedata`.`gid`" }, { "item": "`edgedata`.`gid2`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`edgedata`.`testtype`,`edgedata`.`gid`,`edgedata`.`gid2`" } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ], "index_order_summary": { "table": "`edgedata` FORCE INDEX (PRIMARY)", "index_provides_order": true, "order_direction": "asc", "index": "PRIMARY", "plan_changed": false } } }, { "refine_plan": [ { "table": "`edgedata` FORCE INDEX (PRIMARY)" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)