MariaDB [preprod_fumeur]> SET optimizer_trace='enabled=on'; MariaDB [preprod_fumeur]> explain UPDATE ps_stock_available sa SET sa.reserved_quantity = ( SELECT SUM(od.product_quantity - od.product_quantity_refunded) FROM ps_orders o INNER JOIN ps_order_detail od ON od.id_order = o.id_order INNER JOIN ps_order_state os ON os.id_order_state = o.current_state WHERE o.id_shop = 1 AND os.shipped != 1 AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) ) AND sa.id_product = od.product_id AND sa.id_product_attribute = od.product_attribute_id GROUP BY od.product_id, od.product_attribute_id ) WHERE sa.id_shop = 1 AND sa.id_product IN (SELECT product_id FROM ps_order_detail WHERE id_order = 350730); +------+--------------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------------------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------------------------------------------+-------+-------------+ | 1 | PRIMARY | sa | index | id_shop | PRIMARY | 4 | NULL | 21744 | Using where | | 3 | MATERIALIZED | ps_order_detail | ref | order_detail_order,id_order_id_order_detail,product_id,id_order_product_id | id_order_product_id | 4 | const | 1 | Using index | | 2 | DEPENDENT SUBQUERY | od | ref | order_detail_order,product_attribute_id,id_order_id_order_detail,product_id,id_order_product_id | product_id | 9 | preprod_fumeur.sa.id_product,preprod_fumeur.sa.id_product_attribute | 22 | | | 2 | DEPENDENT SUBQUERY | o | eq_ref | PRIMARY,id_shop,current_state | PRIMARY | 4 | preprod_fumeur.od.id_order | 1 | Using where | | 2 | DEPENDENT SUBQUERY | os | eq_ref | PRIMARY | PRIMARY | 4 | preprod_fumeur.o.current_state | 1 | Using where | +------+--------------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------------------------------------------+-------+-------------+ 5 rows in set (0.003 sec) MariaDB [preprod_fumeur]> select * from information_schema.optimizer_trace limit 1\G *************************** 1. row *************************** QUERY: explain UPDATE ps_stock_available sa SET sa.reserved_quantity = ( SELECT SUM(od.product_quantity - od.product_quantity_refunded) FROM ps_orders o INNER JOIN ps_order_detail od ON od.id_order = o.id_order INNER JOIN ps_order_state os ON os.id_order_state = o.current_state WHERE o.id_shop = 1 AND os.shipped != 1 AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) ) AND sa.id_product = od.product_id AND sa.id_product_attribute = od.product_attribute_id GROUP BY od.product_id, od.product_attribute_id ) WHERE sa.id_shop = 1 AND sa.id_product IN (SELECT product_id FROM ps_order_detail WHERE id_order = 350730) TRACE: { "steps": [ { "join_preparation": { "select_id": 3, "steps": [ { "transformation": { "select_id": 3, "from": "IN (SELECT)", "to": "materialization", "sjm_scan_allowed": true, "possible": true } }, { "expanded_query": "/* select#3 */ select ps_order_detail.product_id from ps_order_detail where ps_order_detail.id_order = 350730" } ] } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select sum(od.product_quantity - od.product_quantity_refunded) from ((ps_orders o join ps_order_detail od on(od.id_order = o.id_order)) join ps_order_state os on(os.id_order_state = o.current_state)) where o.id_shop = 1 and os.shipped <> 1 and (o.valid = 1 or os.id_order_state <> 8 and os.id_order_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id group by od.product_id,od.product_attribute_id" } ] } }, { "join_optimization": { "select_id": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "ps_order_detail.id_order = 350730", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(350730, ps_order_detail.id_order)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(350730, ps_order_detail.id_order)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(350730, ps_order_detail.id_order)" } ] } }, { "table_dependencies": [ { "table": "ps_order_detail", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "ps_order_detail", "field": "id_order", "equals": "350730", "null_rejecting": false }, { "table": "ps_order_detail", "field": "id_order", "equals": "350730", "null_rejecting": false }, { "table": "ps_order_detail", "field": "id_order", "equals": "350730", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "ps_order_detail", "range_analysis": { "table_scan": { "rows": 1010714, "cost": 225050.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "order_detail_order", "usable": true, "key_parts": ["id_order", "id_order_detail"] }, { "index": "product_attribute_id", "usable": false, "cause": "not applicable" }, { "index": "id_order_id_order_detail", "usable": true, "key_parts": ["id_order", "id_order_detail"] }, { "index": "id_tax_rules_group", "usable": false, "cause": "not applicable" }, { "index": "id_order_detail", "usable": false, "cause": "not applicable" }, { "index": "product_id", "usable": false, "cause": "not applicable" }, { "index": "id_order_product_id", "usable": true, "key_parts": ["id_order", "product_id", "id_order_detail"] } ], "best_covering_index_scan": { "index": "id_order_product_id", "cost": 203277.8448, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "order_detail_order", "ranges": ["(350730) <= (id_order) <= (350730)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.34509765, "chosen": true }, { "index": "id_order_id_order_detail", "ranges": ["(350730) <= (id_order) <= (350730)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345146475, "chosen": false, "cause": "cost" }, { "index": "id_order_product_id", "ranges": ["(350730) <= (id_order) <= (350730)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.345146475, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "order_detail_order", "index_scan_cost": 1.00009765, "cumulated_index_scan_cost": 1.00009765, "disk_sweep_cost": 1.008997702, "cumulative_total_cost": 2.009095353, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, "chosen": true }, { "index": "id_order_id_order_detail", "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" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "id_order_product_id", "rows": 1, "ranges": ["(350730) <= (id_order) <= (350730)"] }, "rows_for_plan": 1, "cost_for_plan": 0.345146475, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "order_detail_order", "selectivity_from_index": 9.893996e-7 } ], "selectivity_for_columns": [], "cond_selectivity": 9.893996e-7 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "ps_order_detail", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "order_detail_order", "used_range_estimates": true, "rows": 1, "cost": 1.12509765, "chosen": true }, { "access_type": "ref", "index": "id_order_id_order_detail", "used_range_estimates": true, "rows": 1, "cost": 1.125146475, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "id_order_product_id", "used_range_estimates": true, "rows": 1, "cost": 0.125146475, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.125146475, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 0.325146475, "estimated_join_cardinality": 1 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "ps_order_detail", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "order_detail_order", "used_range_estimates": true, "rows": 1, "cost": 1.12509765, "chosen": true }, { "access_type": "ref", "index": "id_order_id_order_detail", "used_range_estimates": true, "rows": 1, "cost": 1.125146475, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "product_id", "rows": 69, "cost": 70.01094904, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "id_order_product_id", "chosen": true, "rows": 1, "cost": 1.000146475, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 1.000146475, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 1.200146475, "estimated_join_cardinality": 1 } ] }, { "best_join_order": ["ps_order_detail"] }, { "attaching_conditions_to_tables": { "original_condition": "ps_order_detail.id_order = 350730", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "ps_order_detail", "attached": null } ] } } ] } }, { "join_optimization": { "select_id": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "o.id_shop = 1 and os.shipped <> 1 and (o.valid = 1 or os.id_order_state <> 8 and os.id_order_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id and os.id_order_state = o.current_state and od.id_order = o.id_order", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "os.shipped <> 1 and (multiple equal(1, o.valid) or os.id_order_state <> 8 and os.id_order_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id and multiple equal(1, o.id_shop) and multiple equal(os.id_order_state, o.current_state) and multiple equal(od.id_order, o.id_order)" }, { "transformation": "constant_propagation", "resulting_condition": "os.shipped <> 1 and (multiple equal(1, o.valid) or os.id_order_state <> 8 and os.id_order_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id and multiple equal(1, o.id_shop) and multiple equal(os.id_order_state, o.current_state) and multiple equal(od.id_order, o.id_order)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "os.shipped <> 1 and (multiple equal(1, o.valid) or os.id_order_state <> 8 and os.id_order_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id and multiple equal(1, o.id_shop) and multiple equal(os.id_order_state, o.current_state) and multiple equal(od.id_order, o.id_order)" } ] } }, { "table_dependencies": [ { "table": "o", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "od", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "os", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "o", "field": "id_order", "equals": "od.id_order", "null_rejecting": false }, { "table": "o", "field": "id_shop", "equals": "1", "null_rejecting": false }, { "table": "o", "field": "id_order", "equals": "od.id_order", "null_rejecting": false }, { "table": "o", "field": "current_state", "equals": "os.id_order_state", "null_rejecting": false }, { "table": "o", "field": "id_order", "equals": "od.id_order", "null_rejecting": false }, { "table": "od", "field": "id_order", "equals": "o.id_order", "null_rejecting": false }, { "table": "od", "field": "product_attribute_id", "equals": "sa.id_product_attribute", "null_rejecting": true }, { "table": "od", "field": "id_order", "equals": "o.id_order", "null_rejecting": false }, { "table": "od", "field": "product_id", "equals": "sa.id_product", "null_rejecting": false }, { "table": "od", "field": "product_attribute_id", "equals": "sa.id_product_attribute", "null_rejecting": true }, { "table": "od", "field": "id_order", "equals": "o.id_order", "null_rejecting": false }, { "table": "od", "field": "product_id", "equals": "sa.id_product", "null_rejecting": false }, { "table": "os", "field": "id_order_state", "equals": "o.current_state", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "o", "range_analysis": { "table_scan": { "rows": 337737, "cost": 76171.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "id_customer", "usable": false, "cause": "not applicable" }, { "index": "id_cart", "usable": false, "cause": "not applicable" }, { "index": "invoice_number", "usable": false, "cause": "not applicable" }, { "index": "id_carrier", "usable": false, "cause": "not applicable" }, { "index": "id_lang", "usable": false, "cause": "not applicable" }, { "index": "id_currency", "usable": true, "key_parts": ["id_currency", "id_order"] }, { "index": "id_address_delivery", "usable": false, "cause": "not applicable" }, { "index": "id_address_invoice", "usable": false, "cause": "not applicable" }, { "index": "id_shop_group", "usable": false, "cause": "not applicable" }, { "index": "id_shop", "usable": true, "key_parts": ["id_shop", "id_order"] }, { "index": "date_add", "usable": false, "cause": "not applicable" }, { "index": "current_state", "usable": true, "key_parts": ["current_state", "id_order"] }, { "index": "reference", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "id_shop", "ranges": ["(1) <= (id_shop) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 168868, "cost": 202766.235, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [ { "index_name": "id_shop", "selectivity_from_index": 0.49999852 } ], "selectivity_for_columns": [], "cond_selectivity": 0.49999852 }, { "table": "od", "range_analysis": { "table_scan": { "rows": 1010714, "cost": 225050.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "order_detail_order", "usable": false, "cause": "not applicable" }, { "index": "product_attribute_id", "usable": false, "cause": "not applicable" }, { "index": "id_order_id_order_detail", "usable": false, "cause": "not applicable" }, { "index": "id_tax_rules_group", "usable": false, "cause": "not applicable" }, { "index": "id_order_detail", "usable": false, "cause": "not applicable" }, { "index": "product_id", "usable": true, "key_parts": [ "product_id", "product_attribute_id", "id_order_detail" ] }, { "index": "id_order_product_id", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "os", "range_analysis": { "table_scan": { "rows": 47, "cost": 12.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["id_order_state"] }, { "index": "module_name", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "os", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 47, "cost": 1, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 47, "cost": 1, "uses_join_buffering": false } }, "rows_for_plan": 47, "cost_for_plan": 10.4, "rest_of_plan": [ { "plan_prefix": ["os"], "table": "o", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "id_shop", "used_range_estimates": true, "rows": 168868, "cost": 7942652.905, "chosen": true }, { "access_type": "ref", "index": "current_state", "rows": 9381, "cost": 440997.0547, "chosen": true }, { "access_type": "scan", "resulting_rows": 168868, "cost": 42395.8, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 9381, "cost": 440997.0547, "uses_join_buffering": false } }, "rows_for_plan": 440907, "cost_for_plan": 529188.8547, "selectivity": 0.49999852, "rest_of_plan": [ { "plan_prefix": ["os", "o"], "table": "od", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "order_detail_order", "rows": 1, "cost": 440927.2218, "chosen": true }, { "access_type": "ref", "index": "product_attribute_id", "rows": 28, "cost": 6393810.68, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "id_order_id_order_detail", "rows": 1, "cost": 440937.9855, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "product_id", "rows": 22, "cost": 5071185.087, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "id_order_product_id", "rows": 1, "cost": 440937.9855, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 1010714, "cost": 45812, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 440927.2218, "uses_join_buffering": false } }, "rows_for_plan": 220452.8473, "cost_for_plan": 1014206.646, "cost_for_sorting": 220452.8473, "estimated_join_cardinality": 220452.8473 } ] }, { "plan_prefix": ["os"], "table": "od", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "product_attribute_id", "rows": 28, "cost": 1363.144571, "chosen": true }, { "access_type": "ref", "index": "product_id", "rows": 22, "cost": 1081.164077, "chosen": true }, { "access_type": "ref", "index": "id_order_product_id", "rows": 22, "cost": 1085.564077, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 22, "cost": 1081.164077, "uses_join_buffering": false } }, "rows_for_plan": 1034, "cost_for_plan": 1298.364077, "rest_of_plan": [ { "plan_prefix": ["os", "od"], "table": "o", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 22, "chosen": true }, { "access_type": "eq_ref", "index": "id_shop", "rows": 1, "cost": 22, "chosen": false, "cause": "cost" }, { "access_type": "eq_ref", "index": "current_state", "rows": 1, "cost": 1034, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 22, "uses_join_buffering": false } }, "rows_for_plan": 1034, "cost_for_plan": 1527.164077, "selectivity": 0.49999852, "cost_for_sorting": 1034, "estimated_join_cardinality": 516.9984692 } ] } ] }, { "plan_prefix": [], "table": "o", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "id_shop", "used_range_estimates": true, "rows": 168868, "cost": 168992.615, "chosen": true }, { "access_type": "scan", "resulting_rows": 168868, "cost": 42395.8, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 168868, "cost": 42395.8, "uses_join_buffering": false } }, "rows_for_plan": 168868, "cost_for_plan": 76169.4, "pruned_by_cost": true }, { "plan_prefix": [], "table": "od", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "product_attribute_id", "rows": 28, "cost": 29.00307598, "chosen": true }, { "access_type": "ref", "index": "product_id", "rows": 22, "cost": 23.003491, "chosen": true }, { "access_type": "ref", "index": "id_order_product_id", "rows": 22, "cost": 27.403491, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 22, "cost": 23.003491, "uses_join_buffering": false } }, "rows_for_plan": 22, "cost_for_plan": 27.403491, "rest_of_plan": [ { "plan_prefix": ["od"], "table": "o", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 22, "chosen": true }, { "access_type": "eq_ref", "index": "id_shop", "rows": 1, "cost": 22, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "current_state", "rows": 1, "cost": 22.2, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 22, "uses_join_buffering": false } }, "rows_for_plan": 22, "cost_for_plan": 53.803491, "selectivity": 0.49999852, "rest_of_plan": [ { "plan_prefix": ["od", "o"], "table": "os", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 10.99996743, "chosen": true }, { "access_type": "scan", "resulting_rows": 47, "cost": 1, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 10.99996743, "uses_join_buffering": false } }, "rows_for_plan": 10.99996743, "cost_for_plan": 67.00345191, "estimated_join_cardinality": 10.99996743 } ] }, { "plan_prefix": ["od"], "table": "os", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 47, "cost": 1, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 47, "cost": 1, "uses_join_buffering": true } }, "rows_for_plan": 1034, "cost_for_plan": 235.203491, "pruned_by_cost": true } ] } ] }, { "best_join_order": ["od", "o", "os"] }, { "attaching_conditions_to_tables": { "original_condition": "o.id_shop = 1 and os.id_order_state = o.current_state and o.id_order = od.id_order and os.shipped <> 1 and (o.valid = 1 or o.current_state <> 8 and o.current_state <> 6) and sa.id_product = od.product_id and sa.id_product_attribute = od.product_attribute_id", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "od", "attached": null }, { "table": "o", "attached": "o.id_shop = 1 and (o.valid = 1 or o.current_state <> 8 and o.current_state <> 6)" }, { "table": "os", "attached": "os.shipped <> 1" } ] } } ] } }, { "table": "sa", "range_analysis": { "table_scan": { "rows": 21744, "cost": 4447.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["id_stock_available"] }, { "index": "product_sqlstock", "usable": true, "key_parts": [ "id_product", "id_product_attribute", "id_shop", "id_shop_group" ] }, { "index": "id_shop", "usable": true, "key_parts": ["id_shop", "id_stock_available"] }, { "index": "id_shop_group", "usable": true, "key_parts": ["id_shop_group", "id_stock_available"] }, { "index": "id_product", "usable": true, "key_parts": ["id_product", "id_stock_available"] }, { "index": "id_product_attribute", "usable": true, "key_parts": ["id_product_attribute", "id_stock_available"] }, { "index": "id_product_id_shop", "usable": true, "key_parts": ["id_product", "id_shop", "id_stock_available"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "id_shop", "ranges": ["(1) <= (id_shop) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 10872, "cost": 13053.60665, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no join" } } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.001 sec) MariaDB [preprod_fumeur]> select -> JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) as trace -> from information_schema.optimizer_trace\G *************************** 1. row *************************** trace: [ { "range_scan_alternatives": [ { "index": "order_detail_order", "ranges": [ "(350730) <= (id_order) <= (350730)" ], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.34509765, "chosen": true }, { "index": "id_order_id_order_detail", "ranges": [ "(350730) <= (id_order) <= (350730)" ], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 1.345146475, "chosen": false, "cause": "cost" }, { "index": "id_order_product_id", "ranges": [ "(350730) <= (id_order) <= (350730)" ], "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 0.345146475, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "order_detail_order", "index_scan_cost": 1.00009765, "cumulated_index_scan_cost": 1.00009765, "disk_sweep_cost": 1.008997702, "cumulative_total_cost": 2.009095353, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, "chosen": true }, { "index": "id_order_id_order_detail", "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" }, "analyzing_index_merge_union": [ ] }, { "range_scan_alternatives": [ { "index": "id_shop", "ranges": [ "(1) <= (id_shop) <= (1)" ], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 168868, "cost": 202766.235, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [ ] }, { "range_scan_alternatives": [ { "index": "id_shop", "ranges": [ "(1) <= (id_shop) <= (1)" ], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 10872, "cost": 13053.60665, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [ ] } ] 1 row in set (0.001 sec)