id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE temp NULL index PRIMARY PRIMARY 4 NULL 55 100.00 Using index; Using temporary; Using filesort 1 SIMPLE ab NULL eq_ref PRIMARY,lot_id PRIMARY 4 test.temp.ab_id 1 10.00 Using where TRACE {\n "steps": [\n {\n "join_preparation": {\n "select#": 1,\n "steps": [\n {\n "expanded_query": "/* select#1 */ select `ab`.`lot_id` AS `lot_id`,count(0) AS `participants_count` from (`test_bets` `ab` join `temporary_table_2` `temp` on((`ab`.`id` = `temp`.`ab_id`))) where (`ab`.`status` = 1) group by `ab`.`lot_id`"\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#1 */ select `ab`.`lot_id` AS `lot_id`,count(0) AS `participants_count` from `test_bets` `ab` join `temporary_table_2` `temp` where ((`ab`.`status` = 1) and (`ab`.`id` = `temp`.`ab_id`)) group by `ab`.`lot_id`"\n }\n }\n ]\n }\n },\n {\n "join_optimization": {\n "select#": 1,\n "steps": [\n {\n "condition_processing": {\n "condition": "WHERE",\n "original_condition": "((`ab`.`status` = 1) and (`ab`.`id` = `temp`.`ab_id`))",\n "steps": [\n {\n "transformation": "equality_propagation",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n },\n {\n "transformation": "constant_propagation",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n },\n {\n "transformation": "trivial_condition_removal",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n }\n ]\n }\n },\n {\n "substitute_generated_columns": {\n }\n },\n {\n "table_dependencies": [\n {\n "table": "`test_bets` `ab`",\n "row_may_be_null": false,\n "map_bit": 0,\n "depends_on_map_bits": [\n ]\n },\n {\n "table": "`temporary_table_2` `temp`",\n "row_may_be_null": false,\n "map_bit": 1,\n "depends_on_map_bits": [\n ]\n }\n ]\n },\n {\n "ref_optimizer_key_uses": [\n {\n "table": "`test_bets` `ab`",\n "field": "id",\n "equals": "`temp`.`ab_id`",\n "null_rejecting": false\n },\n {\n "table": "`temporary_table_2` `temp`",\n "field": "ab_id",\n "equals": "`ab`.`id`",\n "null_rejecting": false\n }\n ]\n },\n {\n "rows_estimation": [\n {\n "table": "`test_bets` `ab`",\n "const_keys_added": {\n "keys": [\n "lot_id"\n ],\n "cause": "group_by"\n },\n "range_analysis": {\n "table_scan": {\n "rows": 600,\n "cost": 123.1\n },\n "potential_range_indexes": [\n {\n "index": "PRIMARY",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "lot_id",\n "usable": true,\n "key_parts": [\n "lot_id",\n "id"\n ]\n },\n {\n "index": "firm_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "user_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "bidder_firm_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "date",\n "usable": false,\n "cause": "not_applicable"\n }\n ],\n "setup_range_conditions": [\n ],\n "group_index_range": {\n "chosen": false,\n "cause": "not_single_table"\n }\n }\n },\n {\n "table": "`temporary_table_2` `temp`",\n "table_scan": {\n "rows": 55,\n "cost": 1\n }\n }\n ]\n },\n {\n "considered_execution_plans": [\n {\n "plan_prefix": [\n ],\n "table": "`temporary_table_2` `temp`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "PRIMARY",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 55,\n "access_type": "scan",\n "resulting_rows": 55,\n "cost": 12,\n "chosen": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 55,\n "cost_for_plan": 12,\n "rest_of_plan": [\n {\n "plan_prefix": [\n "`temporary_table_2` `temp`"\n ],\n "table": "`test_bets` `ab`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "eq_ref",\n "index": "PRIMARY",\n "rows": 1,\n "cost": 66,\n "chosen": true,\n "cause": "clustered_pk_chosen_by_heuristics"\n },\n {\n "rows_to_scan": 600,\n "access_type": "scan",\n "using_join_cache": true,\n "buffers_needed": 1,\n "resulting_rows": 60,\n "cost": 769.09,\n "chosen": false\n }\n ]\n },\n "condition_filtering_pct": 10,\n "rows_for_plan": 5.5,\n "cost_for_plan": 78,\n "sort_cost": 5.5,\n "new_cost_for_plan": 83.5,\n "chosen": true\n }\n ]\n },\n {\n "plan_prefix": [\n ],\n "table": "`test_bets` `ab`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "PRIMARY",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 600,\n "access_type": "scan",\n "resulting_rows": 60,\n "cost": 121,\n "chosen": true,\n "use_tmp_table": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 60,\n "cost_for_plan": 121,\n "pruned_by_cost": true\n }\n ]\n },\n {\n "attaching_conditions_to_tables": {\n "original_condition": "((`ab`.`id` = `temp`.`ab_id`) and (`ab`.`status` = 1))",\n "attached_conditions_computation": [\n ],\n "attached_conditions_summary": [\n {\n "table": "`temporary_table_2` `temp`",\n "attached": null\n },\n {\n "table": "`test_bets` `ab`",\n "attached": "(`ab`.`status` = 1)"\n }\n ]\n }\n },\n {\n "clause_processing": {\n "clause": "GROUP BY",\n "original_clause": "`ab`.`lot_id`",\n "items": [\n {\n "item": "`ab`.`lot_id`"\n }\n ],\n "resulting_clause_is_simple": false,\n "resulting_clause": "`ab`.`lot_id`"\n }\n },\n {\n "refine_plan": [\n {\n "table": "`temporary_table_2` `temp`"\n },\n {\n "table": "`test_bets` `ab`"\n }\n ]\n }\n ]\n }\n },\n {\n "join_explain": {\n "select#": 1,\n "steps": [\n ]\n }\n }\n ]\n} id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE temp NULL index PRIMARY PRIMARY 4 NULL 55 100.00 Using index; Using temporary; Using filesort 1 SIMPLE ab NULL eq_ref PRIMARY,lot_id PRIMARY 4 test.temp.ab_id 1 10.00 Using where TRACE {\n "steps": [\n {\n "join_preparation": {\n "select#": 1,\n "steps": [\n {\n "expanded_query": "/* select#1 */ select `ab`.`lot_id` AS `lot_id`,count(0) AS `participants_count` from (`temporary_table_2` `temp` join `test_bets` `ab` on((`ab`.`id` = `temp`.`ab_id`))) where (`ab`.`status` = 1) group by `ab`.`lot_id`"\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#1 */ select `ab`.`lot_id` AS `lot_id`,count(0) AS `participants_count` from `temporary_table_2` `temp` join `test_bets` `ab` where ((`ab`.`status` = 1) and (`ab`.`id` = `temp`.`ab_id`)) group by `ab`.`lot_id`"\n }\n }\n ]\n }\n },\n {\n "join_optimization": {\n "select#": 1,\n "steps": [\n {\n "condition_processing": {\n "condition": "WHERE",\n "original_condition": "((`ab`.`status` = 1) and (`ab`.`id` = `temp`.`ab_id`))",\n "steps": [\n {\n "transformation": "equality_propagation",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n },\n {\n "transformation": "constant_propagation",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n },\n {\n "transformation": "trivial_condition_removal",\n "resulting_condition": "(multiple equal(1, `ab`.`status`) and multiple equal(`ab`.`id`, `temp`.`ab_id`))"\n }\n ]\n }\n },\n {\n "substitute_generated_columns": {\n }\n },\n {\n "table_dependencies": [\n {\n "table": "`temporary_table_2` `temp`",\n "row_may_be_null": false,\n "map_bit": 0,\n "depends_on_map_bits": [\n ]\n },\n {\n "table": "`test_bets` `ab`",\n "row_may_be_null": false,\n "map_bit": 1,\n "depends_on_map_bits": [\n ]\n }\n ]\n },\n {\n "ref_optimizer_key_uses": [\n {\n "table": "`temporary_table_2` `temp`",\n "field": "ab_id",\n "equals": "`ab`.`id`",\n "null_rejecting": false\n },\n {\n "table": "`test_bets` `ab`",\n "field": "id",\n "equals": "`temp`.`ab_id`",\n "null_rejecting": false\n }\n ]\n },\n {\n "rows_estimation": [\n {\n "table": "`temporary_table_2` `temp`",\n "table_scan": {\n "rows": 55,\n "cost": 1\n }\n },\n {\n "table": "`test_bets` `ab`",\n "const_keys_added": {\n "keys": [\n "lot_id"\n ],\n "cause": "group_by"\n },\n "range_analysis": {\n "table_scan": {\n "rows": 600,\n "cost": 123.1\n },\n "potential_range_indexes": [\n {\n "index": "PRIMARY",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "lot_id",\n "usable": true,\n "key_parts": [\n "lot_id",\n "id"\n ]\n },\n {\n "index": "firm_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "user_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "bidder_firm_id",\n "usable": false,\n "cause": "not_applicable"\n },\n {\n "index": "date",\n "usable": false,\n "cause": "not_applicable"\n }\n ],\n "setup_range_conditions": [\n ],\n "group_index_range": {\n "chosen": false,\n "cause": "not_single_table"\n }\n }\n }\n ]\n },\n {\n "considered_execution_plans": [\n {\n "plan_prefix": [\n ],\n "table": "`temporary_table_2` `temp`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "PRIMARY",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 55,\n "access_type": "scan",\n "resulting_rows": 55,\n "cost": 12,\n "chosen": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 55,\n "cost_for_plan": 12,\n "rest_of_plan": [\n {\n "plan_prefix": [\n "`temporary_table_2` `temp`"\n ],\n "table": "`test_bets` `ab`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "eq_ref",\n "index": "PRIMARY",\n "rows": 1,\n "cost": 66,\n "chosen": true,\n "cause": "clustered_pk_chosen_by_heuristics"\n },\n {\n "rows_to_scan": 600,\n "access_type": "scan",\n "using_join_cache": true,\n "buffers_needed": 1,\n "resulting_rows": 60,\n "cost": 769.09,\n "chosen": false\n }\n ]\n },\n "condition_filtering_pct": 10,\n "rows_for_plan": 5.5,\n "cost_for_plan": 78,\n "sort_cost": 5.5,\n "new_cost_for_plan": 83.5,\n "chosen": true\n }\n ]\n },\n {\n "plan_prefix": [\n ],\n "table": "`test_bets` `ab`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "PRIMARY",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 600,\n "access_type": "scan",\n "resulting_rows": 60,\n "cost": 121,\n "chosen": true,\n "use_tmp_table": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 60,\n "cost_for_plan": 121,\n "pruned_by_cost": true\n }\n ]\n },\n {\n "attaching_conditions_to_tables": {\n "original_condition": "((`ab`.`id` = `temp`.`ab_id`) and (`ab`.`status` = 1))",\n "attached_conditions_computation": [\n ],\n "attached_conditions_summary": [\n {\n "table": "`temporary_table_2` `temp`",\n "attached": null\n },\n {\n "table": "`test_bets` `ab`",\n "attached": "(`ab`.`status` = 1)"\n }\n ]\n }\n },\n {\n "clause_processing": {\n "clause": "GROUP BY",\n "original_clause": "`ab`.`lot_id`",\n "items": [\n {\n "item": "`ab`.`lot_id`"\n }\n ],\n "resulting_clause_is_simple": false,\n "resulting_clause": "`ab`.`lot_id`"\n }\n },\n {\n "refine_plan": [\n {\n "table": "`temporary_table_2` `temp`"\n },\n {\n "table": "`test_bets` `ab`"\n }\n ]\n }\n ]\n }\n },\n {\n "join_explain": {\n "select#": 1,\n "steps": [\n ]\n }\n }\n ]\n}