Explain Query (mysql)
EXPLAIN SELECT
p.product_id as p_id,
p.gift,
p.alcohol,
p.status as product_status,
p.portion_in_biso,
SUM(IF(p.is_organic = 1 AND sc.certification_id = 1, 1, 0)) as is_organic,
SUM(IF(p.is_organic_USDA = 1 AND sc.certification_id = 2, 1, 0)) as is_organic_USDA,
SUM(IF(p.is_scs_certified = 1 AND sc.certification_id = 3, 1, 0)) as is_scs_certified,
p.conservation_mode_in_basket as b_con,
p.visible_on_marketplace as vis,
CONVERT(TRUNCATE(pi.default_price, 2 ),DECIMAL(7,2)) as price,
/* pi.default_price as price, */
p.units,
p.unit_of_measure as unit,
IF(p.trial_product = 0, pi.is_new, 0) as is_new,
pi.is_featured AS featured,
pi.super_featured,
pi.products_inventory_is_top_new AS is_top_new,
p.trial_product as is_trial_product,
IF(p.product_id = 7386 OR p.product_id = 12230, 1, 0) AS is_free_tote,
IF(p.nutritional_values_status = 4, 1, IF(p_n_v_g_b_a.product_id IS NOT NULL, 2, NULL)) AS nutrional_status_published,
p.name AS p_name,
sub.name AS sub_na,
cat.name AS cat_na,
sp.name AS s_name,
pdr.description as d_des,
IFNULL(mpt.marketplace_product_tag_card_label_fr, "") AS card_tag, p.supplier_id as s_id,
p.image_url AS image_url,
IF(pi.super_featured = 1 AND p.super_aubaines_image_url IS NOT NULL, p.super_aubaines_image_url, 0) AS super_aubaines_image_url,
attributes.attr,
IFNULL(his_favorites.favorite, 0) AS favorite_product,
IF(in_his_basket.defined_retail_price_per_unit_for_default_weight = 0 OR in_his_basket.defined_retail_price_per_unit_for_default_weight IS NULL,
CONVERT(TRUNCATE(pi.current_price, 2 ),DECIMAL(7,2)), CONVERT(TRUNCATE(in_his_basket.defined_retail_price_per_unit_for_default_weight, 2), DECIMAL(7,2))
) AS current_price,
p.weight,
p.is_customizable,
p.charge_by_weight as c_wei,
IF(cat.category_id IN (272, 270) AND p.conservation_mode_in_basket IN (0,1), 1, 0) as rinse,
p.group_type,
cat.category_id,
sub.subcategory_id,
IFNULL(mp.average, sp.average) as avg_r,
IFNULL(mcom.comment_count, scom.comment_count) as c_cou,ROUND(p.average_price_quantity/p.weight*IFNULL(in_his_basket.defined_retail_price_per_unit_for_default_weight,pi.current_price),2) as avg_p_p,
/* ROUND(p.average_price_quantity/p.weight*IFNULL(in_his_basket.defined_retail_price_per_unit_for_default_weight,pi.current_price),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,
apu.average_price_unit as avg_p_u,
COUNT(DISTINCT in_his_basket.order_details_id) as nbr_in_basket,
product_families.families AS t1,
supplier_families.families AS t2,
subcategory_families.families AS t3,
category_families.families AS t4,
Concat(false) AS inBasket,
child.sub_products,
base.default_quantity,
CASE WHEN r_prod.recipe_id IS NOT NULL
THEN 1
ELSE 0
END AS bool_is_in_recipe
FROM
products AS p
LEFT JOIN
suppliers_certifications sc ON sc.supplier_id = p.supplier_id AND sc.status = 1
LEFT JOIN (
SELECT
od.product_id,
od.defined_retail_price_per_unit_for_default_weight,
od.order_details_id
FROM
order_details od
WHERE
od.order_id = 0
GROUP BY
od.product_id
) in_his_basket ON (in_his_basket.product_id = p.product_id)
LEFT JOIN (
SELECT
product_id
FROM
products_nutritional_values_generated_by_ai
GROUP BY
product_id
) p_n_v_g_b_a ON (p_n_v_g_b_a.product_id = p.product_id)
LEFT JOIN (
SELECT
recipeIngredients.recipe_id,
recipeIngredients.product_id
FROM
recipeIngredients
INNER JOIN products ON (products.product_id = recipeIngredients.product_id AND products.status = 1)
WHERE
recipe_id IN (SELECT recipe_id FROM recipes WHERE status = 1)
) as r_prod ON r_prod.product_id = p.product_id
INNER JOIN suppliers AS sp ON p.supplier_id = sp.supplier_id
INNER JOIN products_inventory AS pi ON (pi.product_id = p.product_id AND pi.inventory_date = '2025-04-12' AND (pi.quantity > 0 OR p.group_type = 1 OR in_his_basket.product_id > 0 OR r_prod.recipe_id > 0 OR p.product_id IN (3210,3211,11177,12218,12219,12220,12221,12222,12223,12224,12225,12226,12227,12228,12233,12234,12235,12237,12238,12241,12242,12243,12244,12245,12246,13078,15659,16517,16518,16519,16974,16975,16976,19121, 0, 0)))
LEFT JOIN product_discount_reasons pdr ON pdr.product_discount_reason_id = pi.product_discount_reason_id
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 average_price_units apu ON apu.average_price_unit_id = p.average_price_unit_id
LEFT OUTER JOIN productsLang AS pl ON p.product_id = pl.product_id
LEFT JOIN productSubCategoriesLang subLang ON subLang.subcategory_id = sub.subcategory_id
LEFT JOIN productCategoriesLang catLang ON catLang.category_id = cat.category_id
INNER JOIN suppliersLang AS spLang ON sp.supplier_id = spLang.supplier_id
LEFT JOIN product_discount_reasonsLang pdrl ON pdrl.product_discount_reason_id = pdr.product_discount_reason_id AND pdrl.lang_id = 'fr'
LEFT JOIN (
SELECT product_id, if('fr' = 'fr',GROUP_CONCAT(mf.title separator ';'),GROUP_CONCAT(mfLang.l_title separator ';')) AS families FROM marketplace_family_product mfp
LEFT JOIN marketplace_families mf ON mf.marketplace_family_id = mfp.marketplace_family_id AND mf.title != ''
LEFT JOIN marketplace_families_lang mfLang ON mf.marketplace_family_id = mfLang.marketplace_family_id
WHERE mf.status = 1
GROUP BY mfp.product_id
) product_families ON product_families.product_id = p.product_id
LEFT JOIN (
SELECT supplier_id, if('fr' = 'fr',GROUP_CONCAT(mf.title separator ';'),GROUP_CONCAT(mfLang.l_title separator ';')) as families FROM marketplace_family_supplier mfs
LEFT JOIN marketplace_families mf ON mf.marketplace_family_id = mfs.marketplace_family_id AND mf.title != ''
LEFT JOIN marketplace_families_lang mfLang ON mf.marketplace_family_id = mfLang.marketplace_family_id
WHERE mf.status = 1
GROUP BY mfs.supplier_id
) supplier_families ON supplier_families.supplier_id = p.supplier_id
LEFT JOIN (
SELECT subcategory_id, if('fr' = 'fr',GROUP_CONCAT(mf.title separator ';'),GROUP_CONCAT(mfLang.l_title separator ';')) AS families FROM marketplace_family_subcategory mfsc
LEFT JOIN marketplace_families mf ON mf.marketplace_family_id = mfsc.marketplace_family_id AND mf.title != ''
LEFT JOIN marketplace_families_lang mfLang ON mf.marketplace_family_id = mfLang.marketplace_family_id
WHERE mf.status = 1
GROUP BY mfsc.subcategory_id
) subcategory_families ON subcategory_families.subcategory_id = subsub.subcategory_id
LEFT JOIN (
SELECT category_id, if('fr' = 'fr',GROUP_CONCAT(mf.title separator ';'),GROUP_CONCAT(mfLang.l_title separator ';')) AS families FROM marketplace_family_category mfc
LEFT JOIN marketplace_families mf ON mf.marketplace_family_id = mfc.marketplace_family_id AND mf.title != ''
LEFT JOIN marketplace_families_lang mfLang ON mf.marketplace_family_id = mfLang.marketplace_family_id
WHERE mf.status = 1
GROUP BY mfc.category_id
) category_families ON category_families.category_id = sub.category_id
LEFT JOIN (
SELECT
product_id,
1 AS favorite
FROM favorites
WHERE user_id = -1
) as his_favorites ON his_favorites.product_id = p.product_id
LEFT JOIN (SELECT product_id, ROUND(AVG(rating),0) AS average
FROM product_user_ratings pur
INNER JOIN (SELECT owner_id, user_id, status, parent_comment_id FROM user_comments) uc
ON (pur.user_id = uc.user_id AND pur.product_id = uc.owner_id)
WHERE pur.updated_at >= SUBDATE(CURDATE(), INTERVAL 180 DAY) AND uc.status = 1 AND IFNULL(uc.parent_comment_id, 0) = 0
GROUP BY product_id
) sp ON (sp.product_id = p.product_id)
LEFT JOIN (SELECT product_group_relations.parent_id, ROUND(AVG(rating),0) AS average
FROM (SELECT pur.*, uc.status, uc.parent_comment_id FROM product_user_ratings pur INNER JOIN (SELECT owner_id, user_id, status, parent_comment_id FROM user_comments) uc ON (pur.user_id = uc.user_id AND pur.product_id = uc.owner_id)) inter
JOIN product_group_relations ON (product_group_relations.child_id = inter.product_id)
WHERE inter.updated_at >= SUBDATE(CURDATE(), INTERVAL 180 DAY) AND inter.status = 1 AND IFNULL(inter.parent_comment_id, 0) = 0
GROUP BY product_group_relations.parent_id
) mp ON (mp.parent_id = p.product_id)
LEFT JOIN (SELECT owner_id, COUNT(*) AS comment_count
FROM user_comments
WHERE `owner_name` = 'Product' AND status = 1
GROUP BY owner_id
) scom ON (scom.owner_id = p.product_id)
LEFT JOIN (SELECT product_group_relations.parent_id, GROUP_CONCAT(product_group_relations.child_id ORDER BY CAST(sortedchildrenbyweight.weight AS unsigned), sortedchildrenbyweight.name separator ';') AS sub_products
FROM product_group_relations
LEFT JOIN (SELECT product_id, weight, name FROM products ORDER BY weight ASC) AS sortedchildrenbyweight ON (sortedchildrenbyweight.product_id = product_group_relations.child_id)
GROUP BY product_group_relations.parent_id
) as child ON child.parent_id = p.product_id
LEFT JOIN (SELECT product_group_relations.parent_id, COUNT(*) AS comment_count FROM user_comments
JOIN product_group_relations ON product_group_relations.child_id = user_comments.owner_id
WHERE `owner_name` = 'Product' AND status = 1
GROUP BY product_group_relations.parent_id
) mcom ON (mcom.parent_id = p.product_id)
LEFT JOIN marketplace_product_tag mpt ON (mpt.marketplace_product_tag_product_id = p.product_id AND '2025-04-12' BETWEEN mpt.marketplace_product_tag_start_date AND mpt.marketplace_product_tag_end_date)
LEFT JOIN (
SELECT product_id, default_quantity, basket_design_id
FROM basket_design_product_availability
WHERE basket_design_id IN
(SELECT basket_design_id
FROM orders
WHERE order_id = 0)
AND default_quantity > 0
) base ON base.product_id = p.product_id
LEFT JOIN basket_designs bs ON bs.basket_design_id = base.basket_design_id LEFT JOIN (SELECT product_id,GROUP_CONCAT(attribute_value,'##',attribute_name separator '|') AS attr
FROM product_attributes pa
GROUP by product_id
) as attributes ON attributes.product_id = p.product_id
WHERE
(p.trial_product = 0 OR bs.basket_id = 10) AND
p.preference_restriction IN ('All markets','Vegan') AND
p.restricted_to_lufa IN (0) AND
(p.status = 1 OR (p.status = 0 AND p.gift = 1))
OR in_his_basket.product_id > 0 OR r_prod.recipe_id > 0 OR p.product_id IN (3210,3211,11177,12218,12219,12220,12221,12222,12223,12224,12225,12226,12227,12228,12233,12234,12235,12237,12238,12241,12242,12243,12244,12245,12246,13078,15659,16517,16518,16519,16974,16975,16976,19121, 0, 0)
GROUP BY
p.product_id
ORDER BY
cat.weight ASC, sub.weight ASC, subsub.weight ASC,sp.weight DESC, p.name
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | p | ALL | PRIMARY, idx_status, idx_is_new, idx_featured, idx_restricted_to_lufa, idx_days_alert_before_expiry_date, crop_var, subsub_idx, fk_manipulation_instructions_packer_pap_handling_instructions, idx_group_type, idx_virtual_products, fk_product_tote_format, fk_portioning_speed_group, state, fk_portioning_packaging_new | 15998 | 100.00 | Using where; Using temporary; Using filesort | ||||
| 1 | PRIMARY | spLang | ref | supplier_id | supplier_id | 4 | securelufacom.p.supplier_id | 1 | 100.00 | Using index | |
| 1 | PRIMARY | sp | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.supplier_id | 1 | 100.00 | ||
| 1 | PRIMARY | sc | ALL | 562 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 1 | PRIMARY | pi | eq_ref | idx_uniq_pid_date, idx_pid_date, idx_date | idx_uniq_pid_date | 7 | securelufacom.p.product_id, const | 1 | 100.00 | ||
| 1 | PRIMARY | pdr | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.pi.product_discount_reason_id | 1 | 100.00 | ||
| 1 | PRIMARY | pdrl | ref | product_discount_reason_id | product_discount_reason_id | 5 | securelufacom.pdr.product_discount_reason_id | 1 | 100.00 | Using where | |
| 1 | PRIMARY | subsub | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.sub_sub_id | 1 | 100.00 | ||
| 1 | PRIMARY | sub | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.subsub.subcategory_id | 1 | 100.00 | ||
| 1 | PRIMARY | cat | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.sub.category_id | 1 | 100.00 | ||
| 1 | PRIMARY | apu | eq_ref | PRIMARY | PRIMARY | 4 | securelufacom.p.average_price_unit_id | 1 | 100.00 | ||
| 1 | PRIMARY | pl | ref | productslang_ibfk_1, product_id | productslang_ibfk_1 | 4 | securelufacom.p.product_id | 1 | 100.00 | Using index | |
| 1 | PRIMARY | subLang | ref | subcategory_id | subcategory_id | 4 | securelufacom.sub.subcategory_id | 1 | 100.00 | Using index | |
| 1 | PRIMARY | catLang | ref | category_id | category_id | 4 | securelufacom.cat.category_id | 1 | 100.00 | Using index | |
| 1 | PRIMARY | <derived6> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived7> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.supplier_id | 2 | 100.00 | ||
| 1 | PRIMARY | <derived8> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.subsub.subcategory_id | 5 | 100.00 | ||
| 1 | PRIMARY | <derived2> | ALL | 2 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 1 | PRIMARY | <derived9> | ALL | 6 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 1 | PRIMARY | <derived10> | ALL | 2 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 1 | PRIMARY | <derived11> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived13> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived16> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived17> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived19> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | mpt | ref | fk_marketplace_product_tag_product_id | fk_marketplace_product_tag_product_id | 4 | securelufacom.p.product_id | 1 | 100.00 | Using where | |
| 1 | PRIMARY | <derived20> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 11 | 100.00 | ||
| 1 | PRIMARY | bs | eq_ref | PRIMARY | PRIMARY | 4 | base.basket_design_id | 1 | 100.00 | ||
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | Using index | |
| 1 | PRIMARY | <derived22> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.p.product_id | 10 | 100.00 | ||
| 1 | PRIMARY | <derived4> | ref | <auto_key1> | <auto_key1> | 3 | securelufacom.p.product_id | 16 | 100.00 | Using where | |
| 22 | DERIVED | pa | index | product_id | product_id | 4 | 2901 | 100.00 | |||
| 20 | DERIVED | no matching row in const table | |||||||||
| 19 | DERIVED | user_comments | ref | idx_status | idx_status | 4 | const | 30248 | 10.00 | Using where; Using temporary | |
| 19 | DERIVED | product_group_relations | ref | parent_id, child_id | child_id | 4 | securelufacom.user_comments.owner_id | 1 | 100.00 | ||
| 17 | DERIVED | product_group_relations | ALL | parent_id | 1189 | 100.00 | Using filesort | ||||
| 17 | DERIVED | <derived18> | ref | <auto_key0> | <auto_key0> | 4 | securelufacom.product_group_relations.child_id | 13 | 100.00 | ||
| 18 | DERIVED | products | ALL | 15998 | 100.00 | Using filesort | |||||
| 16 | DERIVED | user_comments | ref | idx_status | idx_status | 4 | const | 30248 | 10.00 | Using where; Using temporary | |
| 13 | DERIVED | <derived14> | ALL | 10081 | 100.00 | Using temporary | |||||
| 13 | DERIVED | product_group_relations | ref | parent_id, child_id | child_id | 4 | inter.product_id | 1 | 100.00 | ||
| 14 | DERIVED | <derived15> | ALL | 30248 | 100.00 | ||||||
| 14 | DERIVED | pur | eq_ref | unique_rating, idx_product_id | unique_rating | 8 | uc.owner_id, uc.user_id | 1 | 33.33 | Using where | |
| 15 | DERIVED | user_comments | ref | idx_status | idx_status | 4 | const | 30248 | 100.00 | Using where | |
| 11 | DERIVED | <derived12> | ALL | 30248 | 100.00 | Using temporary | |||||
| 11 | DERIVED | pur | eq_ref | unique_rating, idx_product_id | unique_rating | 8 | uc.owner_id, uc.user_id | 1 | 33.33 | Using where | |
| 12 | DERIVED | user_comments | ref | idx_status | idx_status | 4 | const | 30248 | 100.00 | Using where | |
| 10 | DERIVED | favorites | ref | unique_favorite | unique_favorite | 4 | const | 1 | 100.00 | Using index | |
| 9 | DERIVED | mfc | ALL | idx_marketplace_family_id | 22 | 100.00 | Using filesort | ||||
| 9 | DERIVED | mf | eq_ref | PRIMARY, status | PRIMARY | 4 | securelufacom.mfc.marketplace_family_id | 1 | 28.18 | Using where | |
| 9 | DERIVED | mfLang | ref | marketplace_family_id | marketplace_family_id | 4 | securelufacom.mfc.marketplace_family_id | 1 | 100.00 | ||
| 8 | DERIVED | mfsc | ALL | idx_marketplace_family_id | 198 | 100.00 | Using filesort | ||||
| 8 | DERIVED | mf | eq_ref | PRIMARY, status | PRIMARY | 4 | securelufacom.mfsc.marketplace_family_id | 1 | 28.18 | Using where | |
| 8 | DERIVED | mfLang | ref | marketplace_family_id | marketplace_family_id | 4 | securelufacom.mfsc.marketplace_family_id | 1 | 100.00 | ||
| 7 | DERIVED | mfs | ALL | idx_marketplace_family_id | 33 | 100.00 | Using filesort | ||||
| 7 | DERIVED | mf | eq_ref | PRIMARY, status | PRIMARY | 4 | securelufacom.mfs.marketplace_family_id | 1 | 28.18 | Using where | |
| 7 | DERIVED | mfLang | ref | marketplace_family_id | marketplace_family_id | 4 | securelufacom.mfs.marketplace_family_id | 1 | 100.00 | ||
| 6 | DERIVED | mfp | ALL | idx_marketplace_family_id | 1719 | 100.00 | Using filesort | ||||
| 6 | DERIVED | mf | eq_ref | PRIMARY, status | PRIMARY | 4 | securelufacom.mfp.marketplace_family_id | 1 | 28.18 | Using where | |
| 6 | DERIVED | mfLang | ref | marketplace_family_id | marketplace_family_id | 4 | securelufacom.mfp.marketplace_family_id | 1 | 100.00 | ||
| 4 | DERIVED | recipes | ref | PRIMARY, status | status | 2 | const | 183 | 100.00 | Using index | |
| 4 | DERIVED | recipeIngredients | ref | recipe_id | recipe_id | 5 | securelufacom.recipes.recipe_id | 28 | 100.00 | Using index condition; Using where | |
| 4 | DERIVED | products | eq_ref | PRIMARY, idx_status | PRIMARY | 4 | securelufacom.recipeIngredients.product_id | 1 | 20.98 | Using where | |
| 3 | DERIVED | products_nutritional_values_generated_by_ai | range | u_idx_p_n_v_g_b_a_product_id_type | u_idx_p_n_v_g_b_a_product_id_type | 4 | 22132 | 100.00 | Using index for group-by | ||
| 2 | DERIVED | od | ref | idx_order_id, idx_product_id | idx_order_id | 4 | const | 1 | 100.00 | Using temporary |