Explain Query (mysql)
EXPLAIN SELECT
od.product_id as p_id,
od.product_id,
od.order_details_id,
od.product_details as description,
IF(od.defined_retail_price_per_unit_for_default_weight < od.default_retail_price_per_unit, 1, NULL) as on_sale,
od.default_retail_price_per_unit as default_price,
od.defined_retail_price_per_unit_for_default_weight as defined_price,
od.default_retail_price_per_unit as price,
od.paid_price_per_unit_for_default_weight as paid_price,
ROUND(p.average_price_quantity/p.weight*od.defined_retail_price_per_unit_for_default_weight,2) as avg_p_p,
ROUND(p.average_price_quantity/p.weight*p.price,2) as avg_p_p_r,
p.average_price_quantity as avg_p_q,
IF(
p.average_price_quantity IS NOT NULL AND
ROUND(p.average_price_quantity/p.weight*od.defined_retail_price_per_unit_for_default_weight,2) IS NOT NULL AND
ROUND(p.average_price_quantity/p.weight*p.price,2) IS NOT NULL AND
apu.average_price_unit IS NOT NULL
, 1, 0) as show_ind,
apu.average_price_unit as avg_p_u,
CONCAT('1') as quantity_in_basket,
IFNULL(pl.l_name, p.name) AS p_name,
IFNULL(spLang.l_name, sp.name) AS s_name,
p.image_url AS image_url,
IFNULL(catLang.l_name, cat.name) AS cat_na,
p.conservation_mode_in_basket as b_con,
p.weight,
p.charge_by_weight as c_wei,
p.units,
p.unit_of_measure as unit,
cat.weight as cat_weight
FROM
order_details od
LEFT JOIN
products p ON p.product_id = od.product_id
LEFT JOIN
average_price_units apu ON apu.average_price_unit_id = p.average_price_unit_id
LEFT JOIN
productsLang AS pl ON p.product_id = pl.product_id AND pl.lang_id = 'fr'
LEFT JOIN
suppliers AS sp ON p.supplier_id = sp.supplier_id
LEFT JOIN
suppliersLang AS spLang ON sp.supplier_id = spLang.supplier_id AND spLang.lang_id = 'fr'
LEFT JOIN
productSubSubCategories subsub ON subsub.sub_sub_id = p.sub_sub_id
LEFT JOIN
productSubCategories sub ON sub.subcategory_id = subsub.subcategory_id
LEFT JOIN
product_categories cat ON cat.category_id = sub.category_id
LEFT JOIN
productCategoriesLang catLang ON catLang.category_id = cat.category_id
WHERE
order_id = '22240692' AND
recipe_id IS NULL
ORDER BY
cat.weight ASC, sub.weight ASC, subsub.weight ASC, p.name
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | od | ref | idx_order_id | idx_order_id | 4 | const | 23 | 10.00 | Using where; Using temporary; Using filesort | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.od.product_id | 1 | 100.00 | ||
| 1 | SIMPLE | apu | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.average_price_unit_id | 1 | 100.00 | ||
| 1 | SIMPLE | pl | ref | productslang_ibfk_1, product_id | productslang_ibfk_1 | 4 | securelufacom.p.product_id | 1 | 100.00 | Using where | |
| 1 | SIMPLE | sp | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.supplier_id | 1 | 100.00 | ||
| 1 | SIMPLE | spLang | ref | supplier_id, lang_id | supplier_id | 4 | securelufacom.sp.supplier_id | 1 | 100.00 | Using where | |
| 1 | SIMPLE | subsub | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.sub_sub_id | 1 | 100.00 | ||
| 1 | SIMPLE | sub | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.subsub.subcategory_id | 1 | 100.00 | ||
| 1 | SIMPLE | cat | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.sub.category_id | 1 | 100.00 | ||
| 1 | SIMPLE | catLang | ref | category_id | category_id | 4 | securelufacom.cat.category_id | 1 | 100.00 |