{ "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `messaggio`.`id` AS `id`,`messaggio`.`data` AS `data`,`allegati`.`id` AS `id` from (((/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50) `m_padre` join `messaggio` on(((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`)))) left join `allegati` on((`messaggio`.`id` = `allegati`.`idpost`))) order by `messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))" } ] } }, { "table_dependencies": [ { "table": "`messaggio` `m`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`messaggio` `m`", "field": "idpadre", "equals": "0", "null_rejecting": false }, { "table": "`messaggio` `m`", "field": "idpadre", "equals": "0", "null_rejecting": false }, { "table": "`messaggio` `m`", "field": "pubblica", "equals": "1", "null_rejecting": false }, { "table": "`messaggio` `m`", "field": "idarticolo", "equals": "'repnews_271330'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`messaggio` `m`", "range_analysis": { "table_scan": { "rows": 2445095, "cost": 567805 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idevento_idx", "usable": false, "cause": "not_applicable" }, { "index": "idpadre", "usable": true, "key_parts": [ "idpadre", "id" ] }, { "index": "ideventoidpadre_idx", "usable": false, "cause": "not_applicable" }, { "index": "ideventoidpadrepubblica_idx", "usable": false, "cause": "not_applicable" }, { "index": "idpadrepubblica_idx", "usable": true, "key_parts": [ "idpadre", "pubblica", "id" ] }, { "index": "ididpadrepubblica", "usable": false, "cause": "not_applicable" }, { "index": "idUtente_idx", "usable": false, "cause": "not_applicable" }, { "index": "data_idx", "usable": false, "cause": "not_applicable" }, { "index": "nome", "usable": false, "cause": "not_applicable" }, { "index": "idbrand_idx", "usable": false, "cause": "not_applicable" }, { "index": "idarticolo_idx", "usable": true, "key_parts": [ "idarticolo", "id" ] }, { "index": "commento_ft_idx", "usable": false, "cause": "not_applicable" }, { "index": "titolo_ft_idx", "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": "idpadre", "ranges": [ "0 <= idpadre <= 0" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1222547, "cost": 1.47e6, "chosen": false, "cause": "cost" }, { "index": "idpadrepubblica_idx", "ranges": [ "0 <= idpadre <= 0 AND 1 <= pubblica <= 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1222547, "cost": 1.47e6, "chosen": false, "cause": "cost" }, { "index": "idarticolo_idx", "ranges": [ "repnews_271330 <= idarticolo <= repnews_271330" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 26, "cost": 32.21, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indices": [ { "index": "idpadrepubblica_idx", "index_scan_cost": 1791, "cumulated_index_scan_cost": 1791, "disk_sweep_cost": 278008, "cumulated_total_cost": 279799, "usable": true, "matching_rows_now": 1.22e6, "isect_covering_with_this_index": false, "chosen": true }, { "index": "idarticolo_idx", "index_scan_cost": 1.4032, "cumulated_index_scan_cost": 1792.4, "disk_sweep_cost": 257, "cumulated_total_cost": 2049.4, "usable": true, "matching_rows_now": 13, "isect_covering_with_this_index": false, "chosen": true }, { "index": "idpadre", "cumulated_total_cost": 2049.4, "usable": false, "cause": "does_not_reduce_cost_of_intersect" } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no_clustered_pk_index" }, "chosen": false, "cause": "cost" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "idarticolo_idx", "rows": 26, "ranges": [ "repnews_271330 <= idarticolo <= repnews_271330" ] }, "rows_for_plan": 26, "cost_for_plan": 32.21, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`messaggio` `m`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idpadre", "rows": 1.22e6, "cost": 480861, "chosen": true }, { "access_type": "ref", "index": "idpadrepubblica_idx", "rows": 1.22e6, "cost": 480861, "chosen": false }, { "access_type": "ref", "index": "idarticolo_idx", "rows": 26, "cost": 31.2, "chosen": true }, { "access_type": "range", "cause": "heuristic_index_cheaper", "chosen": false } ] }, "cost_for_plan": 31.2, "rows_for_plan": 26, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`messaggio` `m`", "attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`m`.`data` desc", "items": [ { "item": "`m`.`data`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`m`.`data` desc" } }, { "refine_plan": [ { "table": "`messaggio` `m`", "pushed_index_condition": "(`m`.`idarticolo` = 'repnews_271330')", "table_condition_attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0))" } ] }, { "added_back_ref_condition": "((`m`.`idarticolo` <=> 'repnews_271330') and ((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0)))" }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`messaggio` `m`", "index_provides_order": false, "order_direction": "undefined", "index": "idarticolo_idx", "plan_changed": false } } } ] } }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `messaggio`.`id` AS `id`,`messaggio`.`data` AS `data`,`allegati`.`id` AS `id` from (/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330')) order by `m`.`data` desc limit 50) `m_padre` join `messaggio` left join `allegati` on((`messaggio`.`id` = `allegati`.`idpost`)) where ((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`)) order by `messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`" } }, { "condition_processing": { "condition": "WHERE", "original_condition": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))" } ] } }, { "table_dependencies": [ { "table": " `m_padre`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`messaggio`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] }, { "table": "`allegati`", "row_may_be_null": true, "map_bit": 2, "depends_on_map_bits": [ 1 ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`allegati`", "field": "idpost", "equals": "`messaggio`.`id`", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": " `m_padre`", "table_scan": { "rows": 26, "cost": 11 } }, { "table": "`messaggio`", "table_scan": { "rows": 2445095, "cost": 78784 } }, { "table": "`allegati`", "table_scan": { "rows": 400357, "cost": 5479 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": " `m_padre`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 26, "cost": 16.5, "chosen": true } ] }, "cost_for_plan": 16.5, "rows_for_plan": 26, "rest_of_plan": [ { "plan_prefix": [ " `m_padre`" ], "table": "`messaggio`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2.45e6, "cost": 1.48e7, "chosen": true } ] }, "cost_for_plan": 1.48e7, "rows_for_plan": 6.36e7, "rest_of_plan": [ { "plan_prefix": [ " `m_padre`", "`messaggio`" ], "table": "`allegati`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idpost_idx", "rows": 1, "cost": 6.36e7, "chosen": true }, { "access_type": "scan", "cause": "covering_index_better_than_full_scan", "chosen": false } ] }, "cost_for_plan": 9.1e7, "rows_for_plan": 6.36e7, "chosen": true } ] } ] }, { "plan_prefix": [ ], "table": "`messaggio`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2.45e6, "cost": 567803, "chosen": true } ] }, "cost_for_plan": 567803, "rows_for_plan": 2.45e6, "rest_of_plan": [ { "plan_prefix": [ "`messaggio`" ], "table": " `m_padre`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 26, "cost": 4.03e7, "chosen": true } ] }, "cost_for_plan": 4.09e7, "rows_for_plan": 6.36e7, "rest_of_plan": [ { "plan_prefix": [ "`messaggio`", " `m_padre`" ], "table": "`allegati`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idpost_idx", "rows": 1, "cost": 6.36e7, "chosen": true }, { "access_type": "scan", "cause": "covering_index_better_than_full_scan", "chosen": false } ] }, "cost_for_plan": 1.17e8, "rows_for_plan": 6.36e7, "pruned_by_cost": true } ] }, { "plan_prefix": [ "`messaggio`" ], "table": "`allegati`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idpost_idx", "rows": 1, "cost": 2.45e6, "chosen": true }, { "access_type": "scan", "cause": "covering_index_better_than_full_scan", "chosen": false } ] }, "cost_for_plan": 3.5e6, "rows_for_plan": 2.45e6, "rest_of_plan": [ { "plan_prefix": [ "`messaggio`", "`allegati`" ], "table": " `m_padre`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 26, "cost": 4.03e7, "chosen": true } ] }, "cost_for_plan": 4.38e7, "rows_for_plan": 6.36e7, "chosen": true } ] } ] } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`messaggio`", "attached": null }, { "table": "`allegati`", "attached": null }, { "table": " `m_padre`", "attached": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`", "items": [ { "item": "`messaggio`.`data`" }, { "item": "`messaggio`.`id`" }, { "item": "`allegati`.`id`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`" } }, { "refine_plan": [ { "table": "`messaggio`", "access_type": "table_scan" }, { "table": "`allegati`" }, { "table": " `m_padre`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": " `m_padre`", "row_length": 5, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 3355443 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`messaggio` `m`", "field": "data" } ], "filesort_priority_queue_optimization": { "limit": 50, "rows_estimate": 26963694, "row_size": 150, "memory_available": 2097152, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 27, "examined_rows": 27, "number_of_tmp_files": 0, "sort_buffer_size": 8058, "sort_mode": "" } } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 13, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 1290555 } } }, { "filesort_information": [ { "direction": "desc", "table": "intermediate_tmp_table", "field": "data" }, { "direction": "desc", "table": "intermediate_tmp_table", "field": "id" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "id" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 27, "examined_rows": 27, "number_of_tmp_files": 0, "sort_buffer_size": 1073, "sort_mode": "" } } ] } } ] }