Sandbox

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 = 'es' 
  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 
  12, 12

Query time 0.00297

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.513521317,
    "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": 123,
                  "cost": 0.02524593,
                  "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": 123,
                  "rows": 1,
                  "cost": 0.12230412,
                  "filtered": 51.62111282,
                  "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": 63.49397013,
                  "rows": 1,
                  "cost": 0.059249147,
                  "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": 63.49397013,
                  "rows": 1,
                  "cost": 0.068260347,
                  "filtered": 100,
                  "attached_condition": "trigcond(descr1.lang_code = 'es')"
                }
              },
              {
                "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": 63.49397013,
                  "rows": 1,
                  "cost": 0.062624548,
                  "filtered": 99.17184448,
                  "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": 62.96814015,
                  "rows": 1,
                  "cost": 0.061556379,
                  "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": 62.96814015,
                  "rows": 1,
                  "cost": 0.057140423,
                  "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": 62.96814015,
                  "rows": 1,
                  "cost": 0.057140423,
                  "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	master_product_offers_count	company_id	original_vendor_id	need_age_verification	full_description
708	hello var test	Fulfilment by Gonje	708	P	0	P	0	4	0	N	<p>asdf</p>
841	Platter Test	Estelle’s Delight Chin-Chin	841	P	0	P	0	2	0	N
228	Suya Spice Pack	Grilllite	228,229	P,V	0,228	P	0	25	0	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>
452	Suya Spice Pack	Fulfilment by Gonje (AMCF)	452	P	0	P	0	35	0	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>
447	Syrup Pump	Zerup	447	P	0	P	0	36	0	N	<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	1	0	2	N
831	Testing Drink		831	P	0	P	1	0	2	N
818	Testing Pie		818	P	0	P	2	0	2	N
774	Testing Wine		774	P	0	P	2	0	2	N
823	Testing Wine		823	P	0	P	1	0	2	N
833	Testing Wine	Estelle’s Delight Chin-Chin	833	P	0	P	2	2	0	N
368	Zerup Bottles (10 pack)	Zerup	368	P	0	P	0	36	0	N