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

Query time 0.00113

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": 33,
      "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
5476	1	336,242,307,306,304,271,270,261,258,234,233,222,217,208,194,181,104,101,95,94,91,88,82,79,71,70,67,56,49,42,41,23,17,15,10,29,1M	SF-M	4.500	B	Shampoo Bars	Herbaria Shampoo Bars produce abundant creamy lather to gently but thoroughly cleanse your hair and leave it soft, shiny, and easy to manage. Our Shampoo bars are handmade with moisturizing, food-grade vegetable oils and rich emollients to keep your hair and scalp healthy.	N	N	0.00	N	R	7,8,9	0	0	0	0	98301	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	N	1749672846	Herbaria