id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL ALL NULL NULL NULL NULL 60 100.00 NULL 1 UPDATE filter NULL eq_ref PRIMARY PRIMARY 8 num.lot_id,const 1 100.00 NULL 2 DERIVED ab NULL ALL PRIMARY,lot_id NULL NULL NULL 600 10.00 Using where; Using temporary 2 DERIVED temp NULL eq_ref PRIMARY PRIMARY 4 test.ab.id 1 100.00 Using index TRACE {\n "steps": [\n {\n "join_preparation": {\n "select#": 2,\n "steps": [\n {\n "expanded_query": "/* select#2 */ select straight_join `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` order by NULL"\n }\n ]\n }\n },\n {\n "derived": {\n "table": " `num`",\n "select#": 2,\n "materialized": true\n }\n },\n {\n "join_preparation": {\n "select#": 1,\n "steps": [\n {\n "expanded_query": "/* select#1 */ select `filter`.`participants_count` AS `participants_count` from (`temporary_table_1` `filter` join (/* select#2 */ select straight_join `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` order by NULL) `num` on(((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))))"\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#2 */ select straight_join `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` order by NULL"\n }\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#1 */ select `filter`.`participants_count` AS `participants_count` from `temporary_table_1` `filter` join (/* select#2 */ select straight_join `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` order by NULL) `num` where ((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))"\n }\n }\n ]\n }\n },\n {\n "join_optimization": {\n "select#": 1,\n "steps": [\n {\n "join_optimization": {\n "select#": 2,\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 0\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": "`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 }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 60,\n "cost_for_plan": 121\n },\n {\n "plan_prefix": [\n "`test_bets` `ab`"\n ],\n "table": "`temporary_table_2` `temp`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "eq_ref",\n "index": "PRIMARY",\n "rows": 1,\n "cost": 72,\n "chosen": true,\n "cause": "clustered_pk_chosen_by_heuristics"\n },\n {\n "access_type": "scan",\n "chosen": false,\n "cause": "covering_index_better_than_full_scan"\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 60,\n "cost_for_plan": 193\n }\n ]\n },\n {\n "attaching_conditions_to_tables": {\n "original_condition": "((`temp`.`ab_id` = `ab`.`id`) and (`ab`.`status` = 1))",\n "attached_conditions_computation": [\n ],\n "attached_conditions_summary": [\n {\n "table": "`test_bets` `ab`",\n "attached": "(`ab`.`status` = 1)"\n },\n {\n "table": "`temporary_table_2` `temp`",\n "attached": null\n }\n ]\n }\n },\n {\n "clause_processing": {\n "clause": "ORDER BY",\n "original_clause": "NULL",\n "items": [\n {\n "item": "NULL",\n "uses_only_constant_tables": true\n }\n ],\n "resulting_clause_is_simple": true,\n "resulting_clause": ""\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": true,\n "resulting_clause": "`ab`.`lot_id`"\n }\n },\n {\n "reconsidering_access_paths_for_index_ordering": {\n "clause": "GROUP BY",\n "index_order_summary": {\n "table": "`test_bets` `ab`",\n "index_provides_order": false,\n "order_direction": "undefined",\n "index": "unknown",\n "plan_changed": false\n }\n }\n },\n {\n "refine_plan": [\n {\n "table": "`test_bets` `ab`"\n },\n {\n "table": "`temporary_table_2` `temp`"\n }\n ]\n }\n ]\n }\n },\n {\n "condition_processing": {\n "condition": "WHERE",\n "original_condition": "((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))",\n "steps": [\n {\n "transformation": "equality_propagation",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n },\n {\n "transformation": "constant_propagation",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n },\n {\n "transformation": "trivial_condition_removal",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n }\n ]\n }\n },\n {\n "substitute_generated_columns": {\n }\n },\n {\n "table_dependencies": [\n {\n "table": "`temporary_table_1` `filter`",\n "row_may_be_null": false,\n "map_bit": 0,\n "depends_on_map_bits": [\n ]\n },\n {\n "table": " `num`",\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_1` `filter`",\n "field": "lot_id",\n "equals": "`num`.`lot_id`",\n "null_rejecting": false\n },\n {\n "table": "`temporary_table_1` `filter`",\n "field": "lot_type",\n "equals": "4",\n "null_rejecting": false\n },\n {\n "table": " `num`",\n "field": "lot_id",\n "equals": "`filter`.`lot_id`",\n "null_rejecting": false\n },\n {\n "table": " `num`",\n "field": "lot_id",\n "equals": "`filter`.`lot_id`",\n "null_rejecting": false\n }\n ]\n },\n {\n "rows_estimation": [\n {\n "table": "`temporary_table_1` `filter`",\n "table_scan": {\n "rows": 1169,\n "cost": 4\n }\n },\n {\n "table": " `num`",\n "table_scan": {\n "rows": 60,\n "cost": 13\n }\n }\n ]\n },\n {\n "considered_execution_plans": [\n {\n "plan_prefix": [\n ],\n "table": " `num`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "",\n "usable": false,\n "chosen": false\n },\n {\n "access_type": "ref",\n "index": "",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 60,\n "access_type": "scan",\n "resulting_rows": 60,\n "cost": 25,\n "chosen": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 60,\n "cost_for_plan": 25,\n "rest_of_plan": [\n {\n "plan_prefix": [\n " `num` id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL ALL NULL NULL NULL NULL 5 100.00 NULL 1 UPDATE filter NULL eq_ref PRIMARY PRIMARY 8 num.lot_id,const 1 100.00 NULL 2 DERIVED temp NULL index PRIMARY PRIMARY 4 NULL 55 100.00 Using index; Using temporary 2 DERIVED 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#": 2,\n "steps": [\n {\n "expanded_query": "/* select#2 */ select straight_join `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` order by NULL"\n }\n ]\n }\n },\n {\n "derived": {\n "table": " `num`",\n "select#": 2,\n "materialized": true\n }\n },\n {\n "join_preparation": {\n "select#": 1,\n "steps": [\n {\n "expanded_query": "/* select#1 */ select `filter`.`participants_count` AS `participants_count` from (`temporary_table_1` `filter` join (/* select#2 */ select straight_join `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` order by NULL) `num` on(((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))))"\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#2 */ select straight_join `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` order by NULL"\n }\n },\n {\n "transformations_to_nested_joins": {\n "transformations": [\n "JOIN_condition_to_WHERE",\n "parenthesis_removal"\n ],\n "expanded_query": "/* select#1 */ select `filter`.`participants_count` AS `participants_count` from `temporary_table_1` `filter` join (/* select#2 */ select straight_join `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` order by NULL) `num` where ((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))"\n }\n }\n ]\n }\n },\n {\n "join_optimization": {\n "select#": 1,\n "steps": [\n {\n "join_optimization": {\n "select#": 2,\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 0\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 },\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 }\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": "ORDER BY",\n "original_clause": "NULL",\n "items": [\n {\n "item": "NULL",\n "uses_only_constant_tables": true\n }\n ],\n "resulting_clause_is_simple": true,\n "resulting_clause": ""\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 "condition_processing": {\n "condition": "WHERE",\n "original_condition": "((`filter`.`lot_id` = `num`.`lot_id`) and (`filter`.`lot_type` = 4))",\n "steps": [\n {\n "transformation": "equality_propagation",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n },\n {\n "transformation": "constant_propagation",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n },\n {\n "transformation": "trivial_condition_removal",\n "resulting_condition": "(multiple equal(`filter`.`lot_id`, `num`.`lot_id`) and multiple equal(4, `filter`.`lot_type`))"\n }\n ]\n }\n },\n {\n "substitute_generated_columns": {\n }\n },\n {\n "table_dependencies": [\n {\n "table": "`temporary_table_1` `filter`",\n "row_may_be_null": false,\n "map_bit": 0,\n "depends_on_map_bits": [\n ]\n },\n {\n "table": " `num`",\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_1` `filter`",\n "field": "lot_id",\n "equals": "`num`.`lot_id`",\n "null_rejecting": false\n },\n {\n "table": "`temporary_table_1` `filter`",\n "field": "lot_type",\n "equals": "4",\n "null_rejecting": false\n },\n {\n "table": " `num`",\n "field": "lot_id",\n "equals": "`filter`.`lot_id`",\n "null_rejecting": false\n },\n {\n "table": " `num`",\n "field": "lot_id",\n "equals": "`filter`.`lot_id`",\n "null_rejecting": false\n }\n ]\n },\n {\n "rows_estimation": [\n {\n "table": "`temporary_table_1` `filter`",\n "table_scan": {\n "rows": 1169,\n "cost": 4\n }\n },\n {\n "table": " `num`",\n "table_scan": {\n "rows": 5,\n "cost": 10\n }\n }\n ]\n },\n {\n "considered_execution_plans": [\n {\n "plan_prefix": [\n ],\n "table": " `num`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "ref",\n "index": "",\n "usable": false,\n "chosen": false\n },\n {\n "access_type": "ref",\n "index": "",\n "usable": false,\n "chosen": false\n },\n {\n "rows_to_scan": 5,\n "access_type": "scan",\n "resulting_rows": 5,\n "cost": 11.25,\n "chosen": true\n }\n ]\n },\n "condition_filtering_pct": 100,\n "rows_for_plan": 5,\n "cost_for_plan": 11.25,\n "rest_of_plan": [\n {\n "plan_prefix": [\n " `num`"\n ],\n "table": "`temporary_table_1` `filter`",\n "best_access_path": {\n "considered_access_paths": [\n {\n "access_type": "eq_ref",\n "index": "PRIMARY",\n