Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # 5.6
- {
- "steps": [
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "expanded_query": "/* select#1 */ select distinct `i`.`Name` AS `Name`,`d`.`id` AS `id`,`i`.`deviceType` AS `deviceType`,`i`.`issuedBy` AS `issuedBy`,`d`.`description` AS `description`,`i`.`avNum` AS `avNum`,`d`.`CompanyName` AS `CompanyName`,`d`.`BrandName` AS `BrandName`,`d`.`dwNumber` AS `dwNumber`,`i`.`quant` AS `quant`,`i`.`discDate` AS `discDate`,`i`.`Type` AS `Type` from (`table_one` `d` join `table_two` `i` on((`d`.`id` = `i`.`id`)))"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "transformations_to_nested_joins": {
- "transformations": [
- "JOIN_condition_to_WHERE",
- "parenthesis_removal"
- ],
- "expanded_query": "/* select#1 */ select distinct `i`.`Name` AS `Name`,`d`.`id` AS `id`,`i`.`deviceType` AS `deviceType`,`i`.`issuedBy` AS `issuedBy`,`d`.`description` AS `description`,`i`.`avNum` AS `avNum`,`d`.`CompanyName` AS `CompanyName`,`d`.`BrandName` AS `BrandName`,`d`.`dwNumber` AS `dwNumber`,`i`.`quant` AS `quant`,`i`.`discDate` AS `discDate`,`i`.`Type` AS `Type` from `table_one` `d` join `table_two` `i` where (`d`.`id` = `i`.`id`)"
- }
- },
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(`d`.`id` = `i`.`id`)",
- "steps": [
- {
- "transformation": "equality_propagation",
- "resulting_condition": "multiple equal(`d`.`id`, `i`.`id`)"
- },
- {
- "transformation": "constant_propagation",
- "resulting_condition": "multiple equal(`d`.`id`, `i`.`id`)"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "multiple equal(`d`.`id`, `i`.`id`)"
- }
- ]
- }
- },
- {
- "table_dependencies": [
- {
- "table": "`table_one` `d`",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ]
- },
- {
- "table": "`table_two` `i`",
- "row_may_be_null": false,
- "map_bit": 1,
- "depends_on_map_bits": [
- ]
- }
- ]
- },
- {
- "ref_optimizer_key_uses": [
- {
- "table": "`table_one` `d`",
- "field": "id",
- "equals": "`i`.`id`",
- "null_rejecting": true
- },
- {
- "table": "`table_two` `i`",
- "field": "id",
- "equals": "`d`.`id`",
- "null_rejecting": false
- }
- ]
- },
- {
- "rows_estimation": [
- {
- "table": "`table_one` `d`",
- "table_scan": {
- "rows": 1596593,
- "cost": 40576
- }
- },
- {
- "table": "`table_two` `i`",
- "table_scan": {
- "rows": 2384739,
- "cost": 12239
- }
- }
- ]
- },
- {
- "considered_execution_plans": [
- {
- "plan_prefix": [
- ],
- "table": "`table_one` `d`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "scan",
- "rows": 1.6e6,
- "cost": 359895,
- "chosen": true
- }
- ]
- },
- "cost_for_plan": 359895,
- "rows_for_plan": 1.6e6,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`table_one` `d`"
- ],
- "table": "`table_two` `i`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "idx_table_two_id",
- "rows": 1,
- "cost": 1.6e6,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 1.79e6,
- "cost": 5.7e11,
- "chosen": false
- }
- ]
- },
- "cost_for_plan": 2.28e6,
- "rows_for_plan": 1.6e6,
- "chosen": true
- }
- ]
- },
- {
- "plan_prefix": [
- ],
- "table": "`table_two` `i`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "idx_table_two_id",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "scan",
- "rows": 2.38e6,
- "cost": 489187,
- "chosen": true
- }
- ]
- },
- "cost_for_plan": 489187,
- "rows_for_plan": 2.38e6,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`table_two` `i`"
- ],
- "table": "`table_one` `d`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "PRIMARY",
- "rows": 1,
- "cost": 2.38e6,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 1.2e6,
- "cost": 5.7e11,
- "chosen": false
- }
- ]
- },
- "cost_for_plan": 3.35e6,
- "rows_for_plan": 2.38e6,
- "pruned_by_cost": true
- }
- ]
- }
- ]
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(`i`.`id` = `d`.`id`)",
- "attached_conditions_computation": [
- ],
- "attached_conditions_summary": [
- {
- "table": "`table_one` `d`",
- "attached": null
- },
- {
- "table": "`table_two` `i`",
- "attached": null
- }
- ]
- }
- },
- {
- "refine_plan": [
- {
- "table": "`table_one` `d`",
- "access_type": "table_scan"
- },
- {
- "table": "`table_two` `i`"
- }
- ]
- }
- ]
- }
- },
- {
- "join_execution": {
- "select#": 1,
- "steps": [
- {
- "creating_tmp_table": {
- "tmp_table_info": {
- "table": "intermediate_tmp_table",
- "row_length": 4566,
- "key_length": 4585,
- "unique_constraint": false,
- "location": "memory (heap)",
- "row_limit_estimate": 3674
- }
- }
- },
- {
- "converting_tmp_table_to_myisam": {
- "cause": "memory_table_size_exceeded",
- "tmp_table_info": {
- "table": "intermediate_tmp_table",
- "row_length": 4570,
- "key_length": 4585,
- "unique_constraint": true,
- "location": "disk (MyISAM)",
- "record_format": "packed"
- }
- }
- }
- ]
- }
- }
- ]
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement