MariaDB [wp]> select * from information_schema.optimizer_trace limit 1\G *************************** 1. row *************************** QUERY: SELECT CASE WHEN p.id IS NULL THEN tr.object_id ELSE p.id END AS product_id, tr.object_id AS parent_product_id, tr.object_id AS effective_product_id, t.term_id as brand_id, t.name AS brand, t.slug as brand_slug, '0' as product_stock, '0' as product_count, MAX(ts.name) as size, MAX(tc.name) as color FROM wp_posts p JOIN wp_term_relationships tr ON (p.id = tr.object_id OR p.post_parent = tr.object_id) AND p.post_type LIKE '%product%' AND p.post_status = 'publish' JOIN wp_term_taxonomy xt ON xt.term_taxonomy_id = tr.term_taxonomy_id AND xt.taxonomy = 'pa_brand_master' JOIN wp_terms t ON t.term_id = xt.term_id LEFT JOIN (SELECT trs.object_id, ts.name FROM wp_term_relationships trs JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id JOIN wp_terms ts ON ts.term_id = xs.term_id WHERE xs.taxonomy IN ('pa_size', 'pa_megethos', 'pa_noumero')) AS ts ON p.id = ts.object_id LEFT JOIN (SELECT trc.object_id, tc.name FROM wp_term_relationships trc JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id JOIN wp_terms tc ON tc.term_id = xc.term_id WHERE xc.taxonomy IN ('pa_color', 'pa_chroma', 'pa_colour')) AS tc ON p.id = tc.object_id GROUP BY p.id, tr.object_id, t.term_id, t.name, t.slug ORDER BY p.id, t.slug DESC TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "ts", "select_id": 2, "algorithm": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select trs.object_id AS object_id,ts.`name` AS `name` from ((wp_term_relationships trs join wp_term_taxonomy xs on(xs.term_taxonomy_id = trs.term_taxonomy_id)) join wp_terms ts on(ts.term_id = xs.term_id)) where xs.taxonomy in ('pa_size','pa_megethos','pa_noumero')" } ] } }, { "derived": { "table": "tc", "select_id": 3, "algorithm": "merged" } }, { "join_preparation": { "select_id": 3, "steps": [ { "expanded_query": "/* select#3 */ select trc.object_id AS object_id,tc.`name` AS `name` from ((wp_term_relationships trc join wp_term_taxonomy xc on(xc.term_taxonomy_id = trc.term_taxonomy_id)) join wp_terms tc on(tc.term_id = xc.term_id)) where xc.taxonomy in ('pa_color','pa_chroma','pa_colour')" } ] } }, { "expanded_query": "/* select#1 */ select case when p.`ID` is null then tr.object_id else p.`ID` end AS product_id,tr.object_id AS parent_product_id,tr.object_id AS effective_product_id,t.term_id AS brand_id,t.`name` AS brand,t.slug AS brand_slug,'0' AS product_stock,'0' AS product_count,max(ts.`name`) AS size,max(tc.`name`) AS color from (((((wp_posts p join wp_term_relationships tr on((p.`ID` = tr.object_id or p.post_parent = tr.object_id) and p.post_type like '%product%' and p.post_status = 'publish')) join wp_term_taxonomy xt on(xt.term_taxonomy_id = tr.term_taxonomy_id and xt.taxonomy = 'pa_brand_master')) join wp_terms t on(t.term_id = xt.term_id)) left join (/* select#2 */ select trs.object_id AS object_id,ts.`name` AS `name` from ((wp_term_relationships trs join wp_term_taxonomy xs on(xs.term_taxonomy_id = trs.term_taxonomy_id)) join wp_terms ts on(ts.term_id = xs.term_id)) where xs.taxonomy in ('pa_size','pa_megethos','pa_noumero')) ts on(p.`ID` = trs.object_id)) left join (/* select#3 */ select trc.object_id AS object_id,tc.`name` AS `name` from ((wp_term_relationships trc join wp_term_taxonomy xc on(xc.term_taxonomy_id = trc.term_taxonomy_id)) join wp_terms tc on(tc.term_id = xc.term_id)) where xc.taxonomy in ('pa_color','pa_chroma','pa_colour')) tc on(p.`ID` = trc.object_id)) group by p.`ID`,tr.object_id,t.term_id,t.`name`,t.slug order by p.`ID`,t.slug desc" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "t.term_id = xt.term_id and xt.term_taxonomy_id = tr.term_taxonomy_id and xt.taxonomy = 'pa_brand_master' and (p.`ID` = tr.object_id or p.post_parent = tr.object_id) and p.post_type like '%product%' and p.post_status = 'publish'", "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "tc", "resulting_condition": "xc.taxonomy in ('pa_color','pa_chroma','pa_colour') and multiple equal(p.`ID`, trc.object_id) and multiple equal(tc.term_id, xc.term_id) and multiple equal(xc.term_taxonomy_id, trc.term_taxonomy_id)" } }, { "build_equal_items": { "condition": "ON expr", "attached_to": "ts", "resulting_condition": "xs.taxonomy in ('pa_size','pa_megethos','pa_noumero') and multiple equal(p.`ID`, trs.object_id) and multiple equal(ts.term_id, xs.term_id) and multiple equal(xs.term_taxonomy_id, trs.term_taxonomy_id)" } }, { "transformation": "equality_propagation", "resulting_condition": "xt.taxonomy = 'pa_brand_master' and (multiple equal(p.`ID`, tr.object_id) or multiple equal(p.post_parent, tr.object_id)) and p.post_type like '%product%' and p.post_status = 'publish' and multiple equal(t.term_id, xt.term_id) and multiple equal(xt.term_taxonomy_id, tr.term_taxonomy_id)" }, { "transformation": "constant_propagation", "resulting_condition": "xt.taxonomy = 'pa_brand_master' and (multiple equal(p.`ID`, tr.object_id) or multiple equal(p.post_parent, tr.object_id)) and p.post_type like '%product%' and p.post_status = 'publish' and multiple equal(t.term_id, xt.term_id) and multiple equal(xt.term_taxonomy_id, tr.term_taxonomy_id)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "xt.taxonomy = 'pa_brand_master' and (multiple equal(p.`ID`, tr.object_id) or multiple equal(p.post_parent, tr.object_id)) and p.post_type like '%product%' and p.post_status = 'publish' and multiple equal(t.term_id, xt.term_id) and multiple equal(xt.term_taxonomy_id, tr.term_taxonomy_id)" } ] } }, { "table_dependencies": [ { "table": "p", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "tr", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "xt", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "t", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "trs", "row_may_be_null": true, "map_bit": 4, "depends_on_map_bits": ["0"] }, { "table": "xs", "row_may_be_null": true, "map_bit": 6, "depends_on_map_bits": ["0"] }, { "table": "ts", "row_may_be_null": true, "map_bit": 7, "depends_on_map_bits": ["0"] }, { "table": "trc", "row_may_be_null": true, "map_bit": 5, "depends_on_map_bits": ["0", "4", "6", "7"] }, { "table": "xc", "row_may_be_null": true, "map_bit": 8, "depends_on_map_bits": ["0", "4", "6", "7"] }, { "table": "tc", "row_may_be_null": true, "map_bit": 9, "depends_on_map_bits": ["0", "4", "6", "7"] } ] }, { "ref_optimizer_key_uses": [ { "table": "tr", "index": "term_taxonomy_id", "field": "term_taxonomy_id", "equals": "xt.term_taxonomy_id", "null_rejecting": false }, { "table": "xt", "index": "PRIMARY", "field": "term_taxonomy_id", "equals": "tr.term_taxonomy_id", "null_rejecting": false }, { "table": "xt", "index": "term_id_taxonomy", "field": "term_id", "equals": "t.term_id", "null_rejecting": false }, { "table": "xt", "index": "term_id_taxonomy", "field": "taxonomy", "equals": "'pa_brand_master'", "null_rejecting": false }, { "table": "xt", "index": "taxonomy", "field": "taxonomy", "equals": "'pa_brand_master'", "null_rejecting": false }, { "table": "xt", "index": "taxonomy", "field": "term_taxonomy_id", "equals": "tr.term_taxonomy_id", "null_rejecting": false }, { "table": "t", "index": "PRIMARY", "field": "term_id", "equals": "xt.term_id", "null_rejecting": false }, { "table": "trs", "index": "PRIMARY", "field": "object_id", "equals": "p.`ID`", "null_rejecting": false }, { "table": "trs", "index": "PRIMARY", "field": "term_taxonomy_id", "equals": "xs.term_taxonomy_id", "null_rejecting": true }, { "table": "trs", "index": "term_taxonomy_id", "field": "term_taxonomy_id", "equals": "xs.term_taxonomy_id", "null_rejecting": true }, { "table": "trs", "index": "term_taxonomy_id", "field": "object_id", "equals": "p.`ID`", "null_rejecting": false }, { "table": "trc", "index": "PRIMARY", "field": "object_id", "equals": "p.`ID`", "null_rejecting": false }, { "table": "trc", "index": "PRIMARY", "field": "term_taxonomy_id", "equals": "xc.term_taxonomy_id", "null_rejecting": true }, { "table": "trc", "index": "term_taxonomy_id", "field": "term_taxonomy_id", "equals": "xc.term_taxonomy_id", "null_rejecting": true }, { "table": "trc", "index": "term_taxonomy_id", "field": "object_id", "equals": "p.`ID`", "null_rejecting": false }, { "table": "xs", "index": "PRIMARY", "field": "term_taxonomy_id", "equals": "trs.term_taxonomy_id", "null_rejecting": true }, { "table": "xs", "index": "term_id_taxonomy", "field": "term_id", "equals": "ts.term_id", "null_rejecting": true }, { "table": "ts", "index": "PRIMARY", "field": "term_id", "equals": "xs.term_id", "null_rejecting": true }, { "table": "xc", "index": "PRIMARY", "field": "term_taxonomy_id", "equals": "trc.term_taxonomy_id", "null_rejecting": true }, { "table": "xc", "index": "term_id_taxonomy", "field": "term_id", "equals": "tc.term_id", "null_rejecting": true }, { "table": "tc", "index": "PRIMARY", "field": "term_id", "equals": "xc.term_id", "null_rejecting": true } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "p", "table_scan": { "rows": 359, "cost": 97 } }, { "table": "tr", "table_scan": { "rows": 92590, "cost": 289 } }, { "table": "xt", "range_analysis": { "table_scan": { "rows": 7122, "cost": 1523.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "term_id_taxonomy", "usable": false, "cause": "not applicable" }, { "index": "taxonomy", "usable": true, "key_parts": ["taxonomy", "term_taxonomy_id"] } ], "best_covering_index_scan": { "index": "term_id_taxonomy", "cost": 1521.092243, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "taxonomy", "ranges": [ "(pa_brand_master) <= (taxonomy) <= (pa_brand_master)" ], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 254, "cost": 306.3728547, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "taxonomy", "rows": 254, "ranges": [ "(pa_brand_master) <= (taxonomy) <= (pa_brand_master)" ] }, "rows_for_plan": 254, "cost_for_plan": 306.3728547, "chosen": true } } }, { "table": "xt", "rowid_filters": [ { "key": "taxonomy", "build_cost": 16.88768378, "rows": 254 } ] }, { "selectivity_for_indexes": [ { "index_name": "taxonomy", "selectivity_from_index": 0.035664139 } ], "selectivity_for_columns": [], "cond_selectivity": 0.035664139 }, { "table": "t", "table_scan": { "rows": 1, "cost": 1 } }, { "table": "trs", "table_scan": { "rows": 92590, "cost": 289 } }, { "table": "xs", "range_analysis": { "table_scan": { "rows": 7122, "cost": 1523.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "term_id_taxonomy", "usable": false, "cause": "not applicable" }, { "index": "taxonomy", "usable": true, "key_parts": ["taxonomy", "term_taxonomy_id"] } ], "best_covering_index_scan": { "index": "term_id_taxonomy", "cost": 1521.092243, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "taxonomy", "ranges": [ "(pa_megethos) <= (taxonomy) <= (pa_megethos)", "(pa_noumero) <= (taxonomy) <= (pa_noumero)", "(pa_size) <= (taxonomy) <= (pa_size)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.000053403, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "taxonomy", "rows": 3, "ranges": [ "(pa_megethos) <= (taxonomy) <= (pa_megethos)", "(pa_noumero) <= (taxonomy) <= (pa_noumero)", "(pa_size) <= (taxonomy) <= (pa_size)" ] }, "rows_for_plan": 3, "cost_for_plan": 4.000053403, "chosen": true } } }, { "table": "xs", "rowid_filters": [ { "key": "taxonomy", "build_cost": 0.428011771, "rows": 3 } ] }, { "selectivity_for_indexes": [ { "index_name": "taxonomy", "selectivity_from_index": 4.2123e-4 } ], "selectivity_for_columns": [], "cond_selectivity": 4.2123e-4 }, { "table": "ts", "table_scan": { "rows": 1, "cost": 1 } }, { "table": "trc", "table_scan": { "rows": 92590, "cost": 289 } }, { "table": "xc", "range_analysis": { "table_scan": { "rows": 7122, "cost": 1523.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "term_id_taxonomy", "usable": false, "cause": "not applicable" }, { "index": "taxonomy", "usable": true, "key_parts": ["taxonomy", "term_taxonomy_id"] } ], "best_covering_index_scan": { "index": "term_id_taxonomy", "cost": 1521.092243, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "taxonomy", "ranges": [ "(pa_chroma) <= (taxonomy) <= (pa_chroma)", "(pa_color) <= (taxonomy) <= (pa_color)", "(pa_colour) <= (taxonomy) <= (pa_colour)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 24, "cost": 29.23542722, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "taxonomy", "rows": 24, "ranges": [ "(pa_chroma) <= (taxonomy) <= (pa_chroma)", "(pa_color) <= (taxonomy) <= (pa_color)", "(pa_colour) <= (taxonomy) <= (pa_colour)" ] }, "rows_for_plan": 24, "cost_for_plan": 29.23542722, "chosen": true } } }, { "table": "xc", "rowid_filters": [ { "key": "taxonomy", "build_cost": 1.298160139, "rows": 24 } ] }, { "selectivity_for_indexes": [ { "index_name": "taxonomy", "selectivity_from_index": 0.00336984 } ], "selectivity_for_columns": [], "cond_selectivity": 0.00336984 }, { "table": "tc", "table_scan": { "rows": 1, "cost": 1 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "get_costs_for_tables": [ { "best_access_path": { "table": "t", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 1, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 1, "uses_join_buffering": false } } }, { "best_access_path": { "table": "xt", "considered_access_paths": [ { "access_type": "ref", "index": "term_id_taxonomy", "rows": 1.79769e308, "cost": 1.79769e308, "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "taxonomy", "used_range_estimates": true, "rows": 254, "cost": 255.5528547, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 254, "cost": 255.5528547, "uses_join_buffering": false } } }, { "best_access_path": { "table": "p", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 359, "cost": 97, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 359, "cost": 97, "uses_join_buffering": false } } }, { "best_access_path": { "table": "tr", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 92590, "cost": 289, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 92590, "cost": 289, "uses_join_buffering": false } } } ] }, { "plan_prefix": [], "table": "t", "rows_for_plan": 1, "cost_for_plan": 1.2, "rest_of_plan": [ { "plan_prefix": ["t"], "get_costs_for_tables": [ { "best_access_path": { "table": "xt", "considered_access_paths": [ { "access_type": "eq_ref", "index": "term_id_taxonomy", "rows": 1, "cost": 1, "chosen": true }, { "access_type": "ref", "index": "taxonomy", "used_range_estimates": true, "rows": 254, "cost": 255.5528547, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 1, "uses_join_buffering": false } } }, { "best_access_path": { "table": "p", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 359, "cost": 97, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 359, "cost": 97, "uses_join_buffering": true } } }, { "best_access_path": { "table": "tr", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 92590, "cost": 289, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 92590, "cost": 289, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t"], "table": "xt", "rows_for_plan": 1, "cost_for_plan": 2.4, "rest_of_plan": [ { "plan_prefix": ["t", "xt"], "get_costs_for_tables": [ { "best_access_path": { "table": "p", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 359, "cost": 97, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 359, "cost": 97, "uses_join_buffering": true } } }, { "best_access_path": { "table": "tr", "considered_access_paths": [ { "access_type": "ref", "index": "term_taxonomy_id", "rows": 6, "cost": 1.001757705, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 6, "cost": 1.001757705, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t", "xt"], "table": "tr", "rows_for_plan": 6, "cost_for_plan": 4.601757705, "rest_of_plan": [ { "plan_prefix": ["t", "xt", "tr"], "get_costs_for_tables": [ { "best_access_path": { "table": "p", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 359, "cost": 97, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 359, "cost": 97, "uses_join_buffering": true } } } ] }, { "plan_prefix": ["t", "xt", "tr"], "table": "p", "rows_for_plan": 2154, "cost_for_plan": 532.4017577, "rest_of_plan": [ { "plan_prefix": ["t", "xt", "tr", "p"], "get_costs_for_tables": [ { "best_access_path": { "table": "ts", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 2154, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 2154, "uses_join_buffering": true } } }, { "best_access_path": { "table": "xs", "considered_access_paths": [ { "access_type": "range", "resulting_rows": 3, "cost": 8616.115029, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, "cost": 8616.115029, "uses_join_buffering": true } } }, { "best_access_path": { "table": "trs", "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 2157.071919, "chosen": true }, { "access_type": "ref", "index": "term_taxonomy_id", "rows": 1, "cost": 2157.271919, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 2157.071919, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t", "xt", "tr", "p"], "table": "trs", "rows_for_plan": 2154, "cost_for_plan": 3120.273677, "rest_of_plan": [ { "plan_prefix": ["t", "xt", "tr", "p", "trs"], "get_costs_for_tables": [ { "best_access_path": { "table": "ts", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 2154, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 2154, "uses_join_buffering": true } } }, { "best_access_path": { "table": "xs", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 359, "chosen": true }, { "access_type": "range", "resulting_rows": 3, "cost": 8616.115029, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 359, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t", "xt", "tr", "p", "trs"], "table": "xs", "rows_for_plan": 2154, "cost_for_plan": 3910.073677, "selectivity": 4.2123e-4, "estimated_join_cardinality": 0.907329402, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs" ], "get_costs_for_tables": [ { "best_access_path": { "table": "ts", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.151221567, "chosen": true }, { "access_type": "scan", "resulting_rows": 1, "cost": 0.907329402, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 0.151221567, "uses_join_buffering": false } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs" ], "table": "ts", "rows_for_plan": 0.907329402, "cost_for_plan": 3910.406364, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts" ], "get_costs_for_tables": [ { "best_access_path": { "table": "tc", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 0.907329402, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 0.907329402, "uses_join_buffering": true } } }, { "best_access_path": { "table": "xc", "considered_access_paths": [ { "access_type": "range", "resulting_rows": 24, "cost": 26.52616269, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 24, "cost": 26.52616269, "uses_join_buffering": true } } }, { "best_access_path": { "table": "trc", "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 0.908623386, "chosen": true }, { "access_type": "ref", "index": "term_taxonomy_id", "rows": 1, "cost": 1.108623386, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.908623386, "uses_join_buffering": false } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts" ], "table": "trc", "rows_for_plan": 0.907329402, "cost_for_plan": 3911.496454, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc" ], "get_costs_for_tables": [ { "best_access_path": { "table": "xc", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 359, "chosen": true }, { "access_type": "range", "resulting_rows": 24, "cost": 26.52616269, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 24, "cost": 26.52616269, "uses_join_buffering": true } } }, { "best_access_path": { "table": "tc", "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 1, "cost": 0.907329402, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 1, "cost": 0.907329402, "uses_join_buffering": true } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc" ], "table": "tc", "rows_for_plan": 0.907329402, "cost_for_plan": 3912.585249, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc", "tc" ], "get_costs_for_tables": [ { "best_access_path": { "table": "xc", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 359, "chosen": true }, { "access_type": "ref", "index": "term_id_taxonomy", "rows": 1, "cost": 0.90894637, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.90894637, "uses_join_buffering": false } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc", "tc" ], "table": "xc", "rows_for_plan": 0.907329402, "cost_for_plan": 3913.675661, "selectivity": 0.00336984, "estimated_join_cardinality": 0.003057555 } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc" ], "table": "xc", "rows_for_plan": 21.77590564, "cost_for_plan": 3942.377797, "pruned_by_cost": true, "current_cost": 3942.377797, "best_cost": 3913.675661 } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts" ], "table": "tc", "rows_for_plan": 0.907329402, "cost_for_plan": 3911.49516, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "tc" ], "get_costs_for_tables": [ { "best_access_path": { "table": "xc", "considered_access_paths": [ { "access_type": "ref", "index": "term_id_taxonomy", "rows": 1, "cost": 0.90894637, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.90894637, "uses_join_buffering": false } } }, { "best_access_path": { "table": "trc", "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 0.908623386, "chosen": true }, { "access_type": "ref", "index": "term_taxonomy_id", "rows": 1, "cost": 1.108623386, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.908623386, "uses_join_buffering": false } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "tc" ], "table": "trc", "rows_for_plan": 0.907329402, "cost_for_plan": 3912.585249, "rest_of_plan": [ { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "tc", "trc" ], "get_costs_for_tables": [ { "best_access_path": { "table": "xc", "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 359, "chosen": true }, { "access_type": "ref", "index": "term_id_taxonomy", "rows": 1, "cost": 0.90894637, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.90894637, "uses_join_buffering": false } } } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "tc", "trc" ], "table": "xc", "rows_for_plan": 0.907329402, "cost_for_plan": 3913.675661, "pruned_by_cost": true, "current_cost": 3913.675661, "best_cost": 3913.675661 } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "tc" ], "table": "xc", "rows_for_plan": 0.907329402, "cost_for_plan": 3912.585572, "pruned_by_heuristic": true } ] }, { "plan_prefix": [ "t", "xt", "tr", "p", "trs", "xs", "ts" ], "table": "xc", "rows_for_plan": 21.77590564, "cost_for_plan": 3941.287708, "pruned_by_cost": true, "current_cost": 3941.287708, "best_cost": 3913.675661 } ] } ] } ] }, { "plan_prefix": ["t", "xt", "tr", "p"], "table": "ts", "rows_for_plan": 2154, "cost_for_plan": 3117.201758, "rest_of_plan": [ { "plan_prefix": ["t", "xt", "tr", "p", "ts"], "get_costs_for_tables": [ { "best_access_path": { "table": "xs", "considered_access_paths": [ { "access_type": "ref", "index": "term_id_taxonomy", "rows": 1, "cost": 2157.838682, "chosen": true }, { "access_type": "range", "resulting_rows": 3, "cost": 8616.115029, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 2157.838682, "uses_join_buffering": false } } }, { "best_access_path": { "table": "trs", "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 2157.071919, "chosen": true }, { "access_type": "ref", "index": "term_taxonomy_id", "rows": 1, "cost": 2157.271919, "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 2157.071919, "uses_join_buffering": false } } } ] }, { "plan_prefix": ["t", "xt", "tr", "p", "ts"], "table": "trs", "rows_for_plan": 2154, "cost_for_plan": 5705.073677, "pruned_by_cost": true, "current_cost": 5705.073677, "best_cost": 3913.675661 }, { "plan_prefix": ["t", "xt", "tr", "p", "ts"], "table": "xs", "rows_for_plan": 2154, "cost_for_plan": 5705.840439, "pruned_by_cost": true, "current_cost": 5705.840439, "best_cost": 3913.675661 } ] }, { "plan_prefix": ["t", "xt", "tr", "p"], "table": "xs", "rows_for_plan": 6462, "cost_for_plan": 10440.91679, "pruned_by_cost": true, "current_cost": 10440.91679, "best_cost": 3913.675661 } ] } ] }, { "plan_prefix": ["t", "xt"], "table": "p", "rows_for_plan": 359, "cost_for_plan": 171.2, "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t"], "table": "p", "rows_for_plan": 359, "cost_for_plan": 170, "pruned_by_heuristic": true }, { "plan_prefix": ["t"], "table": "tr", "rows_for_plan": 92590, "cost_for_plan": 18808.2, "pruned_by_cost": true, "current_cost": 18808.2, "best_cost": 3913.675661 } ] }, { "plan_prefix": [], "table": "xt", "rows_for_plan": 254, "cost_for_plan": 306.3528547, "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "p", "rows_for_plan": 359, "cost_for_plan": 168.8, "pruned_by_heuristic": true }, { "plan_prefix": [], "table": "tr", "rows_for_plan": 92590, "cost_for_plan": 18807, "pruned_by_cost": true, "current_cost": 18807, "best_cost": 3913.675661 } ] }, { "best_join_order": [ "t", "xt", "tr", "p", "trs", "xs", "ts", "trc", "tc", "xc" ] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "xt.term_id = t.term_id and tr.term_taxonomy_id = xt.term_taxonomy_id and xt.taxonomy = 'pa_brand_master' and (p.`ID` = tr.object_id or p.post_parent = tr.object_id) and p.post_type like '%product%' and p.post_status = 'publish'" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "trs", "resulting_condition": "trs.object_id = p.`ID` and ts.term_id = xs.term_id and xs.term_taxonomy_id = trs.term_taxonomy_id and xs.taxonomy in ('pa_size','pa_megethos','pa_noumero')" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "trc", "resulting_condition": "trc.object_id = p.`ID` and xc.term_id = tc.term_id and xc.term_taxonomy_id = trc.term_taxonomy_id and xc.taxonomy in ('pa_color','pa_chroma','pa_colour')" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [ { "table": "p", "range_analysis": { "table_scan": { "rows": 359, "cost": 170.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["ID"] }, { "index": "post_name", "usable": false, "cause": "not applicable" }, { "index": "post_parent", "usable": true, "key_parts": [ "post_parent", "post_type", "post_status", "ID" ] }, { "index": "type_status_date", "usable": false, "cause": "not applicable" }, { "index": "post_author", "usable": false, "cause": "not applicable" } ], "best_covering_index_scan": { "index": "post_parent", "cost": 78.58877022, "chosen": true }, "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "table": "tc", "range_analysis": { "table_scan": { "rows": 1, "cost": 3.2 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["term_id"] }, { "index": "slug", "usable": false, "cause": "not applicable" }, { "index": "name", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } } ], "attached_conditions_summary": [ { "table": "t", "attached": null }, { "table": "xt", "attached": "xt.taxonomy = 'pa_brand_master'" }, { "table": "tr", "attached": null }, { "table": "p", "attached": "(p.`ID` = tr.object_id or p.post_parent = tr.object_id) and p.post_type like '%product%' and p.post_status = 'publish'" }, { "table": "trs", "attached": "trigcond(trs.term_taxonomy_id is not null)" }, { "table": "xs", "attached": "trigcond(xs.taxonomy in ('pa_size','pa_megethos','pa_noumero') and xs.term_id is not null)" }, { "table": "ts", "attached": null }, { "table": "trc", "attached": null }, { "table": "tc", "attached": "trigcond(tc.term_id is not null)" }, { "table": "xc", "attached": "trigcond(xc.term_taxonomy_id = trc.term_taxonomy_id and xc.taxonomy in ('pa_color','pa_chroma','pa_colour'))" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0