SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  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_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  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 
  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 cscart_categories.category_id IN (20) 
  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, 31, 33, 34, 35, 36, 37, 38, 40, 72, 
    73, 74, 78, 89, 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 prices.usergroup_id IN (0, 0, 1) 
  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 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 24

Query time 0.00390

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.54309611,
    "filesort": {
      "sort_key": "descr1.product, products.product_id",
      "temporary_table": {
        "filesort": {
          "sort_key": "case when products.parent_product_id <> 0 then products.parent_product_id else products.product_id end",
          "temporary_table": {
            "nested_loop": [
              {
                "table": {
                  "table_name": "cscart_categories",
                  "access_type": "const",
                  "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "ref": ["const"],
                  "rows": 1,
                  "filtered": 100
                }
              },
              {
                "table": {
                  "table_name": "products_categories",
                  "access_type": "ref",
                  "possible_keys": ["PRIMARY", "pt"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "ref": ["const"],
                  "loops": 1,
                  "rows": 130,
                  "cost": 0.02658902,
                  "filtered": 100
                }
              },
              {
                "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.products_categories.product_id"
                  ],
                  "loops": 130,
                  "rows": 1,
                  "cost": 0.1285652,
                  "filtered": 51.92284012,
                  "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,31,33,34,35,36,37,38,40,72,73,74,78,89,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": "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": 67.49969313,
                  "rows": 1,
                  "cost": 0.062832026,
                  "filtered": 100,
                  "attached_condition": "trigcond(companies.`status` = 'A' or products.company_id = 0)"
                }
              },
              {
                "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.products_categories.product_id",
                    "const"
                  ],
                  "loops": 67.49969313,
                  "rows": 1,
                  "cost": 0.071843226,
                  "filtered": 100,
                  "attached_condition": "trigcond(descr1.lang_code = 'en')"
                }
              },
              {
                "table": {
                  "table_name": "prices",
                  "access_type": "ref",
                  "possible_keys": [
                    "usergroup",
                    "product_id",
                    "lower_limit",
                    "usergroup_id"
                  ],
                  "key": "product_id",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": [
                    "u428615623_ecartifygonje.products_categories.product_id"
                  ],
                  "loops": 67.49969313,
                  "rows": 1,
                  "cost": 0.066523739,
                  "filtered": 99.15433502,
                  "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)",
                  "using_index": true
                }
              },
              {
                "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.products_categories.product_id"
                  ],
                  "loops": 66.9288712,
                  "rows": 1,
                  "cost": 0.065376781,
                  "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.products_categories.product_id",
                    "const",
                    "const"
                  ],
                  "loops": 66.9288712,
                  "rows": 1,
                  "cost": 0.06068306,
                  "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')"
                }
              },
              {
                "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.products_categories.product_id",
                    "const"
                  ],
                  "loops": 66.9288712,
                  "rows": 1,
                  "cost": 0.06068306,
                  "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 = products_categories.product_id)"
                }
              }
            ]
          }
        }
      }
    }
  }
}

Result

product_id product company_name product_ids product_types parent_product_ids product_type parent_product_id master_product_offers_count master_product_id company_id original_vendor_id need_age_verification age_limit full_description short_description
99 Box of 50 (Mixed) Estelle’s Delight Chin-Chin 99 P 0 P 0 0 0 2 0 N 0 <p>Box of 50 with your choice of all your favourite flavors</p>
97 Box's of 50 (Creamy) 97 P 0 P 0 1 0 0 2 N 0
98 Box's of 50 (Fruity) Estelle’s Delight Chin-Chin 98 P 0 P 0 0 0 2 0 N 0
91 Box's of 50 (Nuts & Seeds) Estelle’s Delight Chin-Chin 91 P 0 P 0 0 0 2 0 N 0
96 Box's of 50 (Relish Spices) 96 P 0 P 0 1 0 0 0 N 0
182 Chin-Chin Box of 50 (Mixed) Fulfilment by Gonje 182 P 0 P 0 0 0 4 0 N 0 <p>Box of 50 with your choice of all your favourite flavors</p>
451 Crunchy Chin-Chin (Creamy) Fulfilment by Gonje (AMCF) 451 P 0 P 0 0 0 35 0 N 0 <p>Treat yourself to the rich and creamy flavors of our Chin-Chin, made in Africa's image.</p><p><strong>Product contains milk and the specified flavor and a trace amount of vanilla extract, cocoa powder, chocolate, malted barley, brown sugar and cream liqueur for baileys</strong><strong>.</strong></p><p>100g per pack</p>
454 Crunchy Chin-Chin (Fruity) Fulfilment by Gonje (AMCF) 454 P 0 P 0 0 0 35 0 N 0 <p>Indulge in our fruity Chin-Chin, a delightful snack infused with a medley of vibrant flavours from dried fruits, made in Africa's image.</p><p><strong>Product contains the specified fruit and a trace amounts of cranberry, strawberry, apricots, sultanas, lemon, orange, prunes and other dried fruits as contained in fruit mix.</strong>&nbsp;</p><p>100g per pack</p>
453 Crunchy Chin-Chin (Nuts and Seeds) Fulfilment by Gonje (AMCF) 453 P 0 P 0 0 0 35 0 N 0 <p>This product contains roasted nuts and seeds of various types and flavors that deliver a satisfying crunch with every bite of our Chin-Chin,&nbsp;made in Africa's image.</p><p><strong>Product contains specified nuts and may also contain trace amount of peanut, almond, sesame, pistachios, pecans, macadamia, hazelnut and other varieties of nuts as contained in trail mix.</strong></p><p>100g per pack</p>
457 Crunchy Chin-Chin (Relish Spices) Fulfilment by Gonje (AMCF) 457 P 0 P 0 0 0 35 0 N 0 <p>Experience the bold and aromatic flavours of our Relish Spices Chin-Chin made in Africa’s image.</p><p>Product contains the specified flavour and trace amounts of ginger, nutmeg, cinnamon, chilli pepper, black pepper, onion and suya spice in dried grinded form.&nbsp;</p><p>100g per pack<br></p>
701 hello var test Fulfilment by Gonje 701 P 0 P 0 0 0 4 0 N 0 <p>asdf</p>
702 hello var test Fulfilment by Gonje 702 P 0 P 0 0 0 4 0 N 0 <p>asdf</p>
708 hello var test Fulfilment by Gonje 708 P 0 P 0 0 0 4 0 N 0 <p>asdf</p>
228 Suya Spice Pack Grilllite 228,229 P,V 0,228 P 0 0 0 25 0 N 0 <p>Inspired by the legendary street food of Northern Nigeria, Suya Spice&mdash;also known as Yaji&mdash;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>
452 Suya Spice Pack Fulfilment by Gonje (AMCF) 452 P 0 P 0 0 0 35 0 N 0 <p>Inspired by the legendary street food of Northern Nigeria, Suya Spice&mdash;also known as Yaji&mdash;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>
447 Syrup Pump Zerup 447 P 0 P 0 0 0 36 0 N 0 <p>The Zerup Syrup Pump is designed exclusively for dispensing 10ml of Zerup Zero Sugar Syrups. This easy-to-use pump ensures mess-free pouring, making it perfect for cocktails, desserts, and beverages. Enjoy precise, hassle-free sweetness with every pump!</p>
829 Testing Book 829 P 0 P 0 2 0 0 2 N 0
769 Testing Chips 769 P 0 P 0 2 0 0 2 N 0
831 Testing Drink 831 P 0 P 0 1 0 0 2 N 0
818 Testing Pie 818 P 0 P 0 3 0 0 2 N 0
847 Testing Root 847 P 0 P 0 2 0 0 2 N 0
774 Testing Wine 774 P 0 P 0 2 0 0 2 N 0
833 Testing Wine 1 Estelle’s Delight Chin-Chin 833 P 0 P 0 2 0 2 0 N 0
368 Zerup Bottles (10 pack) Zerup 368 P 0 P 0 0 0 36 0 N 0