Sandbox

SELECT 
  cscart_products.product_id, 
  cscart_products.company_id, 
  GROUP_CONCAT(
    IF(
      cscart_products_categories.link_type = 'M', 
      CONCAT(
        cscart_products_categories.category_id, 
        'M'
      ), 
      cscart_products_categories.category_id
    )
  ) as category_ids, 
  cscart_products.product_code, 
  cscart_products.weight, 
  cscart_products.tracking, 
  cscart_product_descriptions.product, 
  cscart_product_descriptions.short_description, 
  cscart_products.is_edp, 
  cscart_products.edp_shipping, 
  cscart_products.shipping_freight, 
  cscart_products.free_shipping, 
  cscart_products.zero_price_action, 
  cscart_products.tax_ids, 
  cscart_products.qty_step, 
  cscart_products.list_qty_count, 
  cscart_products.max_qty, 
  cscart_products.min_qty, 
  cscart_products.amount as in_stock, 
  cscart_products.shipping_params, 
  cscart_companies.status as company_status, 
  cscart_products.out_of_stock_actions, 
  cscart_products.updated_timestamp, 
  cscart_companies.company as company_name, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, cscart_product_descriptions.product
  ) as product, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.short_description, 
    cscart_product_descriptions.short_description
  ) as short_description 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  AND cscart_product_descriptions.lang_code = 'en' 
  INNER JOIN cscart_products_categories ON cscart_products_categories.product_id = cscart_products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 1) 
  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_products.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_products.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_products.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_products.status IN ('A', 'H') 
  LEFT JOIN cscart_companies ON cscart_companies.company_id = cscart_products.company_id 
  LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = cscart_products.product_id 
  AND shared_descr.company_id = 1 
  AND shared_descr.lang_code = 'en' 
WHERE 
  cscart_products.product_id = 339 
GROUP BY 
  cscart_products.product_id

Query time 0.00176

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_products",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "status"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_product_descriptions",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "9",
      "used_key_parts": ["product_id", "lang_code"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_companies",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "shared_descr",
      "access_type": "system",
      "possible_keys": ["PRIMARY", "product_id", "company_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_products_categories",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 43,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["herbaria_cscart_4_18.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "cscart_categories.storefront_id in (0,1) 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')"
    }
  }
}

Result

product_id	company_id	category_ids	product_code	weight	tracking	product	short_description	is_edp	edp_shipping	shipping_freight	free_shipping	zero_price_action	tax_ids	qty_step	list_qty_count	max_qty	min_qty	in_stock	shipping_params	company_status	out_of_stock_actions	updated_timestamp	company_name
339	1	258,306,304,295,267,265,264,261,255,251,241,240,238,237,236,235,234,233,230,229,192,179,175,123,120,109,101,94,83,79,74,73,69,68,67,64,60,55,54,53,52,47,46,44,43,41,26,20,19,17,16,15,336,1M	L-BIB	8.600	B	Luffa (Loofah) Soaps on a Rope	Welcome to an extraordinary shower. Luffa soap will quickly become your skins BFF. We’ve incorporated our moisturizing soap into whole natural luffas (also spelled loofah). The ones we use come directly to us from Egypt. Luffa aegyptiaca is a member of the Cucurbitaceae (gourd) family that includes squash, pumpkins and zucchini. When young and tender, sponge gourds are eaten. When mature, they are used for a natural scrub. You will see why luffas have been used for thousands of years. They have a perfect texture for the job: not too rough but just right.	N	N	0.00	N	R	7,8,9	0	0	0	0	48620	a:5:{s:16:"min_items_in_box";i:1;s:16:"max_items_in_box";i:80;s:10:"box_length";i:4;s:9:"box_width";i:4;s:10:"box_height";i:2;}	A	N	1749671052	Herbaria