Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  products.amount, 
  descr1.product as product, 
  companies.company as company_name, 
  variation_group_products.group_id AS variation_group_id, 
  products.product_type, 
  products.parent_product_id, 
  products.master_product_offers_count, 
  products.master_product_id, 
  products.company_id, 
  products.original_vendor_id, 
  IF(
    products.age_verification = 'Y', 
    'Y', 
    IF(
      cscart_categories.age_verification = 'Y', 
      'Y', cscart_categories.parent_age_verification
    )
  ) as need_age_verification, 
  IF(
    products.age_limit > cscart_categories.age_limit, 
    IF(
      products.age_limit > cscart_categories.parent_age_limit, 
      products.age_limit, cscart_categories.parent_age_limit
    ), 
    IF(
      cscart_categories.age_limit > cscart_categories.parent_age_limit, 
      cscart_categories.age_limit, cscart_categories.parent_age_limit
    )
  ) as age_limit, 
  descr1.full_description as full_description, 
  descr1.short_description as short_description 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_categories.storefront_id IN (0, 1) 
  LEFT JOIN cscart_warehouses_sum_products_amount as war_sum_amount ON war_sum_amount.product_id = products.product_id 
  LEFT JOIN cscart_warehouses_destination_products_amount AS warehouses_destination_products_amount ON warehouses_destination_products_amount.product_id = products.product_id 
  AND warehouses_destination_products_amount.destination_id = 11 
  AND warehouses_destination_products_amount.storefront_id = 0 
  INNER JOIN cscart_product_variation_group_products AS variation_group_products ON variation_group_products.product_id = products.product_id 
  LEFT JOIN cscart_master_products_storefront_offers_count AS master_products_storefront_offers_count ON master_products_storefront_offers_count.product_id = products.product_id 
  AND master_products_storefront_offers_count.storefront_id = 1 
WHERE 
  1 
  AND (
    companies.status IN ('A') 
    OR products.company_id = 0
  ) 
  AND products.company_id IN (
    2, 3, 4, 7, 14, 15, 21, 23, 25, 26, 27, 28, 
    30, 33, 34, 36, 37, 38, 72, 73, 74, 78, 
    0
  ) 
  AND (
    (
      CASE products.is_stock_split_by_warehouses WHEN 'Y' THEN warehouses_destination_products_amount.amount ELSE products.amount END
    ) > 0 
    OR products.tracking = 'D'
  ) 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND variation_group_products.group_id IN (63, 38, 29, 57, 34, 39, 35, 37, 58, 40) 
  AND products.master_product_status IN ('A') 
  AND products.master_product_id = 0 
  AND (
    products.company_id > 0 
    OR (
      master_products_storefront_offers_count.count > 0
    )
  ) 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  NULL asc

Query time 0.00133

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.30761552,
    "nested_loop": [
      {
        "table": {
          "table_name": "variation_group_products",
          "access_type": "index",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "PRIMARY",
          "key_length": "6",
          "used_key_parts": ["product_id", "group_id"],
          "loops": 1,
          "rows": 161,
          "cost": 0.01515818,
          "filtered": 22.36024857,
          "attached_condition": "variation_group_products.group_id in (63,38,29,57,34,39,35,37,58,40)"
        }
      },
      {
        "table": {
          "table_name": "products",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "status", "idx_master_product_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["u428615623_ecartifygonje.variation_group_products.product_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.04366864,
          "filtered": 51.41223526,
          "attached_condition": "products.master_product_id = 0 and products.company_id in (2,3,4,7,14,15,21,23,25,26,27,28,30,33,34,36,37,38,72,73,74,78,0) and (products.usergroup_ids = '' or find_in_set(0,products.usergroup_ids) or find_in_set(1,products.usergroup_ids)) and products.`status` = 'A' and products.master_product_status = 'A' and products.product_type <> 'D'"
        }
      },
      {
        "table": {
          "table_name": "master_products_storefront_offers_count",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "idx_storefront_id"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["product_id", "storefront_id"],
          "ref": [
            "u428615623_ecartifygonje.variation_group_products.product_id",
            "const"
          ],
          "loops": 36,
          "rows": 1,
          "cost": 0.03301904,
          "filtered": 100,
          "attached_condition": "trigcond(products.company_id > 0 or master_products_storefront_offers_count.count > 0) and trigcond(master_products_storefront_offers_count.product_id = variation_group_products.product_id)"
        }
      },
      {
        "table": {
          "table_name": "descr1",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "product_id"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["product_id", "lang_code"],
          "ref": [
            "u428615623_ecartifygonje.variation_group_products.product_id",
            "const"
          ],
          "loops": 36,
          "rows": 1,
          "cost": 0.04366864,
          "filtered": 100,
          "attached_condition": "trigcond(descr1.lang_code = 'en')"
        }
      },
      {
        "table": {
          "table_name": "companies",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["company_id"],
          "ref": ["u428615623_ecartifygonje.products.company_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.03465744,
          "filtered": 100,
          "attached_condition": "trigcond(companies.`status` = 'A' or products.company_id = 0)"
        }
      },
      {
        "table": {
          "table_name": "products_categories",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "pt"],
          "key": "pt",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["u428615623_ecartifygonje.variation_group_products.product_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.03586178,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["u428615623_ecartifygonje.products_categories.category_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.03301904,
          "filtered": 100,
          "attached_condition": "(cscart_categories.usergroup_ids = '' or find_in_set(0,cscart_categories.usergroup_ids) or find_in_set(1,cscart_categories.usergroup_ids)) and cscart_categories.`status` in ('A','H') and cscart_categories.storefront_id in (0,1)"
        }
      },
      {
        "table": {
          "table_name": "war_sum_amount",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["u428615623_ecartifygonje.variation_group_products.product_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.03554372,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "warehouses_destination_products_amount",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "idx_storefront_id"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["product_id", "destination_id", "storefront_id"],
          "ref": [
            "u428615623_ecartifygonje.variation_group_products.product_id",
            "const",
            "const"
          ],
          "loops": 36,
          "rows": 1,
          "cost": 0.03301904,
          "filtered": 100,
          "attached_condition": "trigcond(case products.is_stock_split_by_warehouses when 'Y' then warehouses_destination_products_amount.amount else products.amount end > 0 or products.tracking = 'D')"
        }
      }
    ]
  }
}

Result

product_id	amount	product	company_name	variation_group_id	product_type	parent_product_id	company_id	need_age_verification	full_description
165	10	Fried Rice (+Choice of Protein)	Adonai Foods	29	P	0	23	N	<p>Fried rice is a flavorful dish made by stir-frying cooked rice with vegetables, protein (such as eggs, chicken, shrimp, or beef), and seasonings.</p>
193	100	Ogbono Soup (+Poundo Yam or Amala)	Adonai Foods	39	P	0	23	N	<p>Ogbono Soup</p>
196	100	Egusi Soup (+Poundo Yam or Amala)	Adonai Foods	38	P	0	23	N	<p>A thick Nigerian soup made from melon seeds, vegetables, and protein.</p>
200	10	Vegetable Soup (Eforiro) (+Poundo Yam or Amala)	Adonai Foods	40	P	0	23	N
205	10	Fried Rice (+Choice of Protein)	Adonai Foods	29	V	165	23	N	<p>Fried rice is a flavorful dish made by stir-frying cooked rice with vegetables, protein (such as eggs, chicken, shrimp, or beef), and seasonings.</p>
206	10	Fried Rice (+Choice of Protein)	Adonai Foods	29	V	165	23	N	<p>Fried rice is a flavorful dish made by stir-frying cooked rice with vegetables, protein (such as eggs, chicken, shrimp, or beef), and seasonings.</p>
221	10	Jollof Rice	Adonai Foods	34	P	0	23	N
222	10	Jollof Rice	Adonai Foods	34	V	221	23	N
223	10	Jollof Rice	Adonai Foods	34	V	221	23	N
224	10	Plantain (+Choice of Protein)	Adonai Foods	35	P	0	23	N
225	10	Plantain (+Choice of Protein)	Adonai Foods	35	V	224	23	N
226	10	Plantain (+Choice of Protein)	Adonai Foods	35	V	224	23	N
228	8	Suya Spice Pack	Grilllite	37	P	0	25	N	<p>Inspired by the legendary street food of Northern Nigeria, Suya Spice—also known as Yaji—is a rich, smoky blend of ground peanuts, chili, garlic, ginger, and traditional spices. Born from the culinary heritage of the Hausa people, this iconic seasoning has flavored grilled meats for generations. Perfect for BBQ, roasting, or adding a bold West African kick to any dish.</p>
229	10	Suya Spice Pack	Grilllite	37	V	228	25	N	<p>Inspired by the legendary street food of Northern Nigeria, Suya Spice—also known as Yaji—is a rich, smoky blend of ground peanuts, chili, garlic, ginger, and traditional spices. Born from the culinary heritage of the Hausa people, this iconic seasoning has flavored grilled meats for generations. Perfect for BBQ, roasting, or adding a bold West African kick to any dish.</p>
230	100	Egusi Soup (+Poundo Yam or Amala)	Adonai Foods	38	V	196	23	N	<p>A thick Nigerian soup made from melon seeds, vegetables, and protein.</p>
231	100	Egusi Soup (+Poundo Yam or Amala)	Adonai Foods	38	V	196	23	N	<p>A thick Nigerian soup made from melon seeds, vegetables, and protein.</p>
232	100	Ogbono Soup (+Poundo Yam or Amala)	Adonai Foods	39	V	193	23	N	<p>Ogbono Soup</p>
233	100	Ogbono Soup (+Poundo Yam or Amala)	Adonai Foods	39	V	193	23	N	<p>Ogbono Soup</p>
234	10	Vegetable Soup (Eforiro) (+Poundo Yam or Amala)	Adonai Foods	40	V	200	23	N
235	10	Vegetable Soup (Eforiro) (+Poundo Yam or Amala)	Adonai Foods	40	V	200	23	N
237	10	Fried Rice (+Choice of Protein)	Adonai Foods	29	V	165	23	N	<p>Fried rice is a flavorful dish made by stir-frying cooked rice with vegetables, protein (such as eggs, chicken, shrimp, or beef), and seasonings.</p>
238	10	Jollof Rice	Adonai Foods	34	V	221	23	N
239	10	Jollof Rice	Adonai Foods	34	V	221	23	N
240	10	Plantain (+Choice of Protein)	Adonai Foods	35	V	224	23	N
241	10	Plantain (+Choice of Protein)	Adonai Foods	35	V	224	23	N
701	1	hello var test	Fulfilment by Gonje	57	P	0	4	N	<p>asdf</p>
702	1	hello var test	Fulfilment by Gonje	57	P	0	4	N	<p>asdf</p>
708	1	hello var test	Fulfilment by Gonje	57	P	0	4	N	<p>asdf</p>
727	100	T-Shirt	Fulfilment by Gonje	58	P	0	4	N
728	100	T-Shirt	Fulfilment by Gonje	58	P	0	4	N
729	100	T-Shirt	Fulfilment by Gonje	58	P	0	4	N
730	100	T-Shirt	Fulfilment by Gonje	58	P	0	4	N
731	100	T-Shirt	Fulfilment by Gonje	58	P	0	4	N
813	1	checkout test product	Testing Stripe	63	P	0	72	N
814	1	checkout test product	Testing Stripe	63	P	0	72	N