*************************** 1. row *************************** QUERY: explain SELECT installation_id, node_id, gateway_id, relay_gateway_id, signal_id, time, rec_time FROM IDontKnoo_1203 WHERE installation_id=594100 AND time>'2014-12-13 22:20:59' AND node_id=34267405 AND (gateway_id<>34267405 OR relay_gateway_id<>34267405) AND signal_id<>12 AND rec_seqno>=0 ORDER BY time LIMIT 1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `IDontKnoo_1203`.`installation_id` AS `installation_id`,`IDontKnoo_1203`.`node_id` AS `node_id`,`IDontKnoo_1203`.`gateway_id` AS `gateway_id`,`IDontKnoo_1203`.`relay_gateway_id` AS `relay_gateway_id`,`IDontKnoo_1203`.`signal_id` AS `signal_id`,`IDontKnoo_1203`.`time` AS `time`,`IDontKnoo_1203`.`rec_time` AS `rec_time` from `IDontKnoo_1203` where ((`IDontKnoo_1203`.`installation_id` = 594100) and (`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and (`IDontKnoo_1203`.`node_id` = 34267405) and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0)) order by `IDontKnoo_1203`.`time` limit 1" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`IDontKnoo_1203`.`installation_id` = 594100) and (`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and (`IDontKnoo_1203`.`node_id` = 34267405) and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0) and multiple equal(594100, `IDontKnoo_1203`.`installation_id`) and multiple equal(34267405, `IDontKnoo_1203`.`node_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0) and multiple equal(594100, `IDontKnoo_1203`.`installation_id`) and multiple equal(34267405, `IDontKnoo_1203`.`node_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0) and multiple equal(594100, `IDontKnoo_1203`.`installation_id`) and multiple equal(34267405, `IDontKnoo_1203`.`node_id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`IDontKnoo_1203`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`IDontKnoo_1203`", "field": "installation_id", "equals": "594100", "null_rejecting": false }, { "table": "`IDontKnoo_1203`", "field": "installation_id", "equals": "594100", "null_rejecting": false }, { "table": "`IDontKnoo_1203`", "field": "installation_id", "equals": "594100", "null_rejecting": false }, { "table": "`IDontKnoo_1203`", "field": "installation_id", "equals": "594100", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`IDontKnoo_1203`", "range_analysis": { "table_scan": { "rows": 139215335, "cost": 2.88e7 }, "potential_range_indexes": [ { "index": "IDontKnoo_time_ix", "usable": true, "key_parts": [ "time" ] }, { "index": "IDontKnoo_insid_time_ix", "usable": true, "key_parts": [ "installation_id", "time" ] }, { "index": "IDontKnoo_insid_sid_time_ix", "usable": true, "key_parts": [ "installation_id", "signal_id", "time" ] }, { "index": "IDontKnoo_insid_sid_zone_time_ix", "usable": true, "key_parts": [ "installation_id", "signal_id", "zone", "time" ] }, { "index": "IDontKnoo_insid_zone_time_ix", "usable": true, "key_parts": [ "installation_id", "zone", "time" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "IDontKnoo_time_ix", "ranges": [ "0x99949b653b < time" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 67900768, "cost": 8.15e7, "chosen": false, "cause": "cost" }, { "index": "IDontKnoo_insid_time_ix", "ranges": [ "594100 <= installation_id <= 594100 AND 0x99949b653b < time" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 90254, "cost": 108306, "chosen": true }, { "index": "IDontKnoo_insid_sid_time_ix", "ranges": [ "594100 <= installation_id <= 594100 AND signal_id < 12", "594100 <= installation_id <= 594100 AND 12 < signal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 77625, "cost": 93152, "chosen": true }, { "index": "IDontKnoo_insid_sid_zone_time_ix", "ranges": [ "594100 <= installation_id <= 594100 AND signal_id < 12", "594100 <= installation_id <= 594100 AND 12 < signal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 78157, "cost": 93790, "chosen": false, "cause": "cost" }, { "index": "IDontKnoo_insid_zone_time_ix", "ranges": [ "594100 <= installation_id <= 594100" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 65715, "cost": 78859, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "IDontKnoo_insid_zone_time_ix", "rows": 65715, "ranges": [ "594100 <= installation_id <= 594100" ] }, "rows_for_plan": 65715, "cost_for_plan": 78859, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`IDontKnoo_1203`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "IDontKnoo_insid_time_ix", "rows": 90254, "cost": 108305, "chosen": true }, { "access_type": "ref", "index": "IDontKnoo_insid_sid_time_ix", "rows": 77625, "cost": 93150, "chosen": true }, { "access_type": "ref", "index": "IDontKnoo_insid_sid_zone_time_ix", "rows": 78157, "cost": 93788, "chosen": false }, { "access_type": "ref", "index": "IDontKnoo_insid_zone_time_ix", "rows": 65715, "cost": 78858, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "IDontKnoo_insid_zone_time_ix" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 1.4484, "rows_for_plan": 951.84, "cost_for_plan": 78858, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`IDontKnoo_1203`.`node_id` = 34267405) and (`IDontKnoo_1203`.`installation_id` = 594100) and (`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`IDontKnoo_1203`", "attached": "((`IDontKnoo_1203`.`node_id` = 34267405) and (`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`IDontKnoo_1203`.`time`", "items": [ { "item": "`IDontKnoo_1203`.`time`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`IDontKnoo_1203`.`time`" } }, { "added_back_ref_condition": "((`IDontKnoo_1203`.`installation_id` <=> 594100) and ((`IDontKnoo_1203`.`node_id` = 34267405) and (`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59') and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0)))" }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "rows_estimation": { "table": "`IDontKnoo_1203`", "index": "IDontKnoo_time_ix", "range_analysis": { "table_scan": { "rows": 139215335, "cost": 1.67e8 }, "potential_range_indexes": [ { "index": "IDontKnoo_time_ix", "usable": true, "key_parts": [ "time" ] }, { "index": "IDontKnoo_insid_time_ix", "usable": false, "cause": "not_applicable" }, { "index": "IDontKnoo_insid_sid_time_ix", "usable": false, "cause": "not_applicable" }, { "index": "IDontKnoo_insid_sid_zone_time_ix", "usable": false, "cause": "not_applicable" }, { "index": "IDontKnoo_insid_zone_time_ix", "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": "IDontKnoo_time_ix", "ranges": [ "0x99949b653b < time" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 67900768, "cost": 8.15e7, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "IDontKnoo_time_ix", "rows": 67900768, "ranges": [ "0x99949b653b < time" ] }, "rows_for_plan": 67900768, "cost_for_plan": 8.15e7, "chosen": true } } }, "index_order_summary": { "table": "`IDontKnoo_1203`", "index_provides_order": true, "order_direction": "asc", "index": "IDontKnoo_time_ix", "plan_changed": true, "access_type": "range" } } }, { "refine_plan": [ { "table": "`IDontKnoo_1203`", "pushed_index_condition": "(`IDontKnoo_1203`.`time` > '2014-12-13 22:20:59')", "table_condition_attached": "((`IDontKnoo_1203`.`installation_id` <=> 594100) and ((`IDontKnoo_1203`.`node_id` = 34267405) and ((`IDontKnoo_1203`.`gateway_id` <> 34267405) or (`IDontKnoo_1203`.`relay_gateway_id` <> 34267405)) and (`IDontKnoo_1203`.`signal_id` <> 12) and (`IDontKnoo_1203`.`rec_seqno` >= 0)))" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)