select * from MFR_PRIVATE_FORUM_T privatefor0_ left outer join MFR_TOPIC_T topicsset1_ on privatefor0_.ID=topicsset1_.pf_surrogateKey left outer join MFR_TOPIC_T childrenfo2_ on topicsset1_.ID=childrenfo2_.pt_surrogateKey where privatefor0_.OWNER='9e4cc57c-91a1-462d-ae8c-574c357577a4' and privatefor0_.surrogateKey=12883 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `privatefor0_`.`ID` AS `ID`,`privatefor0_`.`VERSION` AS `VERSION`,`privatefor0_`.`UUID` AS `UUID`,`privatefor0_`.`CREATED` AS `CREATED`,`privatefor0_`.`CREATED_BY` AS `CREATED_BY`,`privatefor0_`.`MODIFIED` AS `MODIFIED`,`privatefor0_`.`MODIFIED_BY` AS `MODIFIED_BY`,`privatefor0_`.`TITLE` AS `TITLE`,`privatefor0_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`privatefor0_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`privatefor0_`.`TYPE_UUID` AS `TYPE_UUID`,`privatefor0_`.`SORT_INDEX` AS `SORT_INDEX`,`privatefor0_`.`OWNER` AS `OWNER`,`privatefor0_`.`AUTO_FORWARD` AS `AUTO_FORWARD`,`privatefor0_`.`AUTO_FORWARD_EMAIL` AS `AUTO_FORWARD_EMAIL`,`privatefor0_`.`PREVIEW_PANE_ENABLED` AS `PREVIEW_PANE_ENABLED`,`privatefor0_`.`surrogateKey` AS `surrogateKey`,`topicsset1_`.`ID` AS `ID`,`topicsset1_`.`TOPIC_DTYPE` AS `TOPIC_DTYPE`,`topicsset1_`.`VERSION` AS `VERSION`,`topicsset1_`.`UUID` AS `UUID`,`topicsset1_`.`CREATED` AS `CREATED`,`topicsset1_`.`CREATED_BY` AS `CREATED_BY`,`topicsset1_`.`MODIFIED` AS `MODIFIED`,`topicsset1_`.`MODIFIED_BY` AS `MODIFIED_BY`,`topicsset1_`.`DEFAULTASSIGNNAME` AS `DEFAULTASSIGNNAME`,`topicsset1_`.`TITLE` AS `TITLE`,`topicsset1_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`topicsset1_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`topicsset1_`.`MODERATED` AS `MODERATED`,`topicsset1_`.`AUTO_MARK_THREADS_READ` AS `AUTO_MARK_THREADS_READ`,`topicsset1_`.`MUTABLE` AS `MUTABLE`,`topicsset1_`.`SORT_INDEX` AS `SORT_INDEX`,`topicsset1_`.`TYPE_UUID` AS `TYPE_UUID`,`topicsset1_`.`AVAILABILITY_RESTRICTED` AS `AVAILABILITY_RESTRICTED`,`topicsset1_`.`AVAILABILITY` AS `AVAILABILITY`,`topicsset1_`.`OPEN_DATE` AS `OPEN_DATE`,`topicsset1_`.`CLOSE_DATE` AS `CLOSE_DATE`,`topicsset1_`.`of_surrogateKey` AS `of_surrogateKey`,`topicsset1_`.`pf_surrogateKey` AS `pf_surrogateKey`,`topicsset1_`.`USER_ID` AS `USER_ID`,`topicsset1_`.`CONTEXT_ID` AS `CONTEXT_ID`,`topicsset1_`.`pt_surrogateKey` AS `pt_surrogateKey`,`topicsset1_`.`LOCKED` AS `LOCKED`,`topicsset1_`.`DRAFT` AS `DRAFT`,`topicsset1_`.`CONFIDENTIAL_RESPONSES` AS `CONFIDENTIAL_RESPONSES`,`topicsset1_`.`MUST_RESPOND_BEFORE_READING` AS `MUST_RESPOND_BEFORE_READING`,`topicsset1_`.`HOUR_BEFORE_RESPONSES_VISIBLE` AS `HOUR_BEFORE_RESPONSES_VISIBLE`,`topicsset1_`.`POST_FIRST` AS `POST_FIRST`,`childrenfo2_`.`ID` AS `ID`,`childrenfo2_`.`TOPIC_DTYPE` AS `TOPIC_DTYPE`,`childrenfo2_`.`VERSION` AS `VERSION`,`childrenfo2_`.`UUID` AS `UUID`,`childrenfo2_`.`CREATED` AS `CREATED`,`childrenfo2_`.`CREATED_BY` AS `CREATED_BY`,`childrenfo2_`.`MODIFIED` AS `MODIFIED`,`childrenfo2_`.`MODIFIED_BY` AS `MODIFIED_BY`,`childrenfo2_`.`DEFAULTASSIGNNAME` AS `DEFAULTASSIGNNAME`,`childrenfo2_`.`TITLE` AS `TITLE`,`childrenfo2_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`childrenfo2_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`childrenfo2_`.`MODERATED` AS `MODERATED`,`childrenfo2_`.`AUTO_MARK_THREADS_READ` AS `AUTO_MARK_THREADS_READ`,`childrenfo2_`.`MUTABLE` AS `MUTABLE`,`childrenfo2_`.`SORT_INDEX` AS `SORT_INDEX`,`childrenfo2_`.`TYPE_UUID` AS `TYPE_UUID`,`childrenfo2_`.`AVAILABILITY_RESTRICTED` AS `AVAILABILITY_RESTRICTED`,`childrenfo2_`.`AVAILABILITY` AS `AVAILABILITY`,`childrenfo2_`.`OPEN_DATE` AS `OPEN_DATE`,`childrenfo2_`.`CLOSE_DATE` AS `CLOSE_DATE`,`childrenfo2_`.`of_surrogateKey` AS `of_surrogateKey`,`childrenfo2_`.`pf_surrogateKey` AS `pf_surrogateKey`,`childrenfo2_`.`USER_ID` AS `USER_ID`,`childrenfo2_`.`CONTEXT_ID` AS `CONTEXT_ID`,`childrenfo2_`.`pt_surrogateKey` AS `pt_surrogateKey`,`childrenfo2_`.`LOCKED` AS `LOCKED`,`childrenfo2_`.`DRAFT` AS `DRAFT`,`childrenfo2_`.`CONFIDENTIAL_RESPONSES` AS `CONFIDENTIAL_RESPONSES`,`childrenfo2_`.`MUST_RESPOND_BEFORE_READING` AS `MUST_RESPOND_BEFORE_READING`,`childrenfo2_`.`HOUR_BEFORE_RESPONSES_VISIBLE` AS `HOUR_BEFORE_RESPONSES_VISIBLE`,`childrenfo2_`.`POST_FIRST` AS `POST_FIRST` from ((`mfr_private_forum_t` `privatefor0_` left join `mfr_topic_t` `topicsset1_` on((`privatefor0_`.`ID` = `topicsset1_`.`pf_surrogateKey`))) left join `mfr_topic_t` `childrenfo2_` on((`topicsset1_`.`ID` = `childrenfo2_`.`pt_surrogateKey`))) where ((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and (`privatefor0_`.`surrogateKey` = 12883))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "transformations_to_nested_joins": { "transformations": [ "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `privatefor0_`.`ID` AS `ID`,`privatefor0_`.`VERSION` AS `VERSION`,`privatefor0_`.`UUID` AS `UUID`,`privatefor0_`.`CREATED` AS `CREATED`,`privatefor0_`.`CREATED_BY` AS `CREATED_BY`,`privatefor0_`.`MODIFIED` AS `MODIFIED`,`privatefor0_`.`MODIFIED_BY` AS `MODIFIED_BY`,`privatefor0_`.`TITLE` AS `TITLE`,`privatefor0_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`privatefor0_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`privatefor0_`.`TYPE_UUID` AS `TYPE_UUID`,`privatefor0_`.`SORT_INDEX` AS `SORT_INDEX`,`privatefor0_`.`OWNER` AS `OWNER`,`privatefor0_`.`AUTO_FORWARD` AS `AUTO_FORWARD`,`privatefor0_`.`AUTO_FORWARD_EMAIL` AS `AUTO_FORWARD_EMAIL`,`privatefor0_`.`PREVIEW_PANE_ENABLED` AS `PREVIEW_PANE_ENABLED`,`privatefor0_`.`surrogateKey` AS `surrogateKey`,`topicsset1_`.`ID` AS `ID`,`topicsset1_`.`TOPIC_DTYPE` AS `TOPIC_DTYPE`,`topicsset1_`.`VERSION` AS `VERSION`,`topicsset1_`.`UUID` AS `UUID`,`topicsset1_`.`CREATED` AS `CREATED`,`topicsset1_`.`CREATED_BY` AS `CREATED_BY`,`topicsset1_`.`MODIFIED` AS `MODIFIED`,`topicsset1_`.`MODIFIED_BY` AS `MODIFIED_BY`,`topicsset1_`.`DEFAULTASSIGNNAME` AS `DEFAULTASSIGNNAME`,`topicsset1_`.`TITLE` AS `TITLE`,`topicsset1_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`topicsset1_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`topicsset1_`.`MODERATED` AS `MODERATED`,`topicsset1_`.`AUTO_MARK_THREADS_READ` AS `AUTO_MARK_THREADS_READ`,`topicsset1_`.`MUTABLE` AS `MUTABLE`,`topicsset1_`.`SORT_INDEX` AS `SORT_INDEX`,`topicsset1_`.`TYPE_UUID` AS `TYPE_UUID`,`topicsset1_`.`AVAILABILITY_RESTRICTED` AS `AVAILABILITY_RESTRICTED`,`topicsset1_`.`AVAILABILITY` AS `AVAILABILITY`,`topicsset1_`.`OPEN_DATE` AS `OPEN_DATE`,`topicsset1_`.`CLOSE_DATE` AS `CLOSE_DATE`,`topicsset1_`.`of_surrogateKey` AS `of_surrogateKey`,`topicsset1_`.`pf_surrogateKey` AS `pf_surrogateKey`,`topicsset1_`.`USER_ID` AS `USER_ID`,`topicsset1_`.`CONTEXT_ID` AS `CONTEXT_ID`,`topicsset1_`.`pt_surrogateKey` AS `pt_surrogateKey`,`topicsset1_`.`LOCKED` AS `LOCKED`,`topicsset1_`.`DRAFT` AS `DRAFT`,`topicsset1_`.`CONFIDENTIAL_RESPONSES` AS `CONFIDENTIAL_RESPONSES`,`topicsset1_`.`MUST_RESPOND_BEFORE_READING` AS `MUST_RESPOND_BEFORE_READING`,`topicsset1_`.`HOUR_BEFORE_RESPONSES_VISIBLE` AS `HOUR_BEFORE_RESPONSES_VISIBLE`,`topicsset1_`.`POST_FIRST` AS `POST_FIRST`,`childrenfo2_`.`ID` AS `ID`,`childrenfo2_`.`TOPIC_DTYPE` AS `TOPIC_DTYPE`,`childrenfo2_`.`VERSION` AS `VERSION`,`childrenfo2_`.`UUID` AS `UUID`,`childrenfo2_`.`CREATED` AS `CREATED`,`childrenfo2_`.`CREATED_BY` AS `CREATED_BY`,`childrenfo2_`.`MODIFIED` AS `MODIFIED`,`childrenfo2_`.`MODIFIED_BY` AS `MODIFIED_BY`,`childrenfo2_`.`DEFAULTASSIGNNAME` AS `DEFAULTASSIGNNAME`,`childrenfo2_`.`TITLE` AS `TITLE`,`childrenfo2_`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,`childrenfo2_`.`EXTENDED_DESCRIPTION` AS `EXTENDED_DESCRIPTION`,`childrenfo2_`.`MODERATED` AS `MODERATED`,`childrenfo2_`.`AUTO_MARK_THREADS_READ` AS `AUTO_MARK_THREADS_READ`,`childrenfo2_`.`MUTABLE` AS `MUTABLE`,`childrenfo2_`.`SORT_INDEX` AS `SORT_INDEX`,`childrenfo2_`.`TYPE_UUID` AS `TYPE_UUID`,`childrenfo2_`.`AVAILABILITY_RESTRICTED` AS `AVAILABILITY_RESTRICTED`,`childrenfo2_`.`AVAILABILITY` AS `AVAILABILITY`,`childrenfo2_`.`OPEN_DATE` AS `OPEN_DATE`,`childrenfo2_`.`CLOSE_DATE` AS `CLOSE_DATE`,`childrenfo2_`.`of_surrogateKey` AS `of_surrogateKey`,`childrenfo2_`.`pf_surrogateKey` AS `pf_surrogateKey`,`childrenfo2_`.`USER_ID` AS `USER_ID`,`childrenfo2_`.`CONTEXT_ID` AS `CONTEXT_ID`,`childrenfo2_`.`pt_surrogateKey` AS `pt_surrogateKey`,`childrenfo2_`.`LOCKED` AS `LOCKED`,`childrenfo2_`.`DRAFT` AS `DRAFT`,`childrenfo2_`.`CONFIDENTIAL_RESPONSES` AS `CONFIDENTIAL_RESPONSES`,`childrenfo2_`.`MUST_RESPOND_BEFORE_READING` AS `MUST_RESPOND_BEFORE_READING`,`childrenfo2_`.`HOUR_BEFORE_RESPONSES_VISIBLE` AS `HOUR_BEFORE_RESPONSES_VISIBLE`,`childrenfo2_`.`POST_FIRST` AS `POST_FIRST` from `mfr_private_forum_t` `privatefor0_` left join `mfr_topic_t` `topicsset1_` on((`privatefor0_`.`ID` = `topicsset1_`.`pf_surrogateKey`)) left join `mfr_topic_t` `childrenfo2_` on((`topicsset1_`.`ID` = `childrenfo2_`.`pt_surrogateKey`)) where ((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and (`privatefor0_`.`surrogateKey` = 12883))" } }, { "condition_processing": { "condition": "WHERE", "original_condition": "((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and (`privatefor0_`.`surrogateKey` = 12883))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and multiple equal(12883, `privatefor0_`.`surrogateKey`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and multiple equal(12883, `privatefor0_`.`surrogateKey`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`privatefor0_`.`OWNER` = '9e4cc57c-91a1-462d-ae8c-574c357577a4') and multiple equal(12883, `privatefor0_`.`surrogateKey`))" } ] } }, { "table_dependencies": [ { "table": "`mfr_private_forum_t` `privatefor0_`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`mfr_topic_t` `topicsset1_`", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": [ 0 ] }, { "table": "`mfr_topic_t` `childrenfo2_`", "row_may_be_null": true, "map_bit": 2, "depends_on_map_bits": [ 0, 1 ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`mfr_private_forum_t` `privatefor0_`", "field": "OWNER", "equals": "'9e4cc57c-91a1-462d-ae8c-574c357577a4'", "null_rejecting": false }, { "table": "`mfr_private_forum_t` `privatefor0_`", "field": "surrogateKey", "equals": "12883", "null_rejecting": false }, { "table": "`mfr_private_forum_t` `privatefor0_`", "field": "surrogateKey", "equals": "12883", "null_rejecting": false }, { "table": "`mfr_topic_t` `topicsset1_`", "field": "pf_surrogateKey", "equals": "`privatefor0_`.`ID`", "null_rejecting": false }, { "table": "`mfr_topic_t` `childrenfo2_`", "field": "pt_surrogateKey", "equals": "`topicsset1_`.`ID`", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`mfr_private_forum_t` `privatefor0_`", "rows": 1, "cost": 1, "table_type": "const", "empty": false }, { "table": "`mfr_topic_t` `topicsset1_`", "range_analysis": { "table_scan": { "rows": 636617, "cost": 151897 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "FK863DC0BE82FAB29", "usable": true, "key_parts": [ "pf_surrogateKey", "ID" ] }, { "index": "FK863DC0BEFF3B3AE9", "usable": false, "cause": "not_applicable" }, { "index": "FK863DC0BE74C7E92B", "usable": false, "cause": "not_applicable" }, { "index": "MFR_TOPIC_T_UUID_I", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "FK863DC0BE82FAB29", "ranges": [ "156151 <= pf_surrogateKey <= 156151" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 4, "cost": 5.81, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "FK863DC0BE82FAB29", "rows": 4, "ranges": [ "156151 <= pf_surrogateKey <= 156151" ] }, "rows_for_plan": 4, "cost_for_plan": 5.81, "chosen": true } } }, { "table": "`mfr_topic_t` `childrenfo2_`", "table_scan": { "rows": 636617, "cost": 24571 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ "`mfr_private_forum_t` `privatefor0_`" ], "table": "`mfr_topic_t` `topicsset1_`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "FK863DC0BE82FAB29", "rows": 4, "cost": 4.8, "chosen": true }, { "access_type": "range", "cause": "heuristic_index_cheaper