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 = 205 
GROUP BY 
  cscart_products.product_id

Query time 0.00096

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": 14,
      "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
205	1	261,257,241,237,234,201,200,80,67,57,54,49,46,40,27,25,20,18,15,306,1M	F-BW	4.500	D	Brown Windsor Soap	The basis of our Brown Windsor soap comes from Perfumery and Kindred Arts, a Comprehensive Treatise on Perfumery by Richard Cristiani, published in Philadelphia in 1877. Even then, the author referred to the recipe as Old Brown Windsor Soap. The original soap was made in Windsor, England, and exported around the world. Brown Windsor was said to be a favorite of Napoleon, a notorious lover of scent. Cristianis recipe called for lavender, bergamot, caraway, petitgrain, cinnamon, and clove. We tested various proportions of these essential oils and chose the blend we found most satisfying.	N	N	0.00	N	R	7,8,9	0	0	0	0	1000	a:5:{s:16:"min_items_in_box";i:1;s:16:"max_items_in_box";i:100;s:10:"box_length";i:4;s:9:"box_width";i:3;s:10:"box_height";i:1;}	A	B	1743781663	Herbaria