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

Query time 0.02498

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": 8,
      "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
280	1	269,267,264,191,82,67,44,34,33,29,306,228M	B-JJ8U	2.250	B	Jojoba Oil	Jojoba mimics sebum, the waxy secretions from human skin. Considered one of the finest moisturizers, it waterproofs and protects your skin from daily irritants. Jojoba absorbs quickly, leaves no oily coating, and contains beneficial Vitamin E (natural tocopherols) and small amounts of phospholipids.	N	N	0.00	N	R	7,8,9	0	0	0	0	999693	a:5:{s:16:"min_items_in_box";i:1;s:16:"max_items_in_box";i:100;s:10:"box_length";i:8;s:9:"box_width";i:3;s:10:"box_height";i:2;}	A	N	1744216397	Herbaria