Sandbox

SELECT 
  cscart_products.*, 
  cscart_product_descriptions.*, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    CASE WHEN (
      cscart_products_categories.link_type = 'M'
    ) THEN CONCAT(
      cscart_products_categories.category_id, 
      'M'
    ) ELSE cscart_products_categories.category_id END 
    ORDER BY 
      cscart_categories.storefront_id IN (0, 1) DESC, 
      (
        cscart_products_categories.link_type = 'M'
      ) DESC, 
      cscart_products_categories.category_position ASC, 
      cscart_products_categories.category_id ASC
  ) as category_ids, 
  popularity.total as popularity, 
  IF(
    shared_prices.product_id IS NOT NULL, 
    MIN(
      IF(
        shared_prices.percentage_discount = 0, 
        shared_prices.price, 
        shared_prices.price - (
          shared_prices.price * shared_prices.percentage_discount
        )/ 100
      )
    ), 
    MIN(
      IF(
        cscart_product_prices.percentage_discount = 0, 
        cscart_product_prices.price, 
        cscart_product_prices.price - (
          cscart_product_prices.price * cscart_product_prices.percentage_discount
        )/ 100
      )
    )
  ) as price, 
  companies.company as company_name, 
  cscart_product_sales.amount as sales_amount, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  cscart_discussion.type as discussion_type 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  AND cscart_product_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_ult_product_prices shared_prices ON shared_prices.product_id = cscart_products.product_id 
  AND shared_prices.company_id = 1 
  AND shared_prices.lower_limit = 1 
  AND shared_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_companies as companies ON companies.company_id = cscart_products.company_id 
  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_product_popularity as popularity ON popularity.product_id = cscart_products.product_id 
  LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = cscart_products.product_id 
  AND cscart_product_sales.category_id = 398 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 12245 
  AND cscart_seo_names.type = 'p' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  AND cscart_seo_names.company_id = 1 
  LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id 
  AND cscart_discussion.object_type = 'P' 
  AND cscart_discussion.company_id = 1 
WHERE 
  cscart_products.product_id = 12245 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00166

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "shared_prices",
          "access_type": "system",
          "possible_keys": [
            "usergroup",
            "product_id",
            "company_id",
            "lower_limit",
            "usergroup_id"
          ],
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true
        }
      },
      {
        "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": "companies",
          "access_type": "system",
          "possible_keys": ["PRIMARY"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "popularity",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "total"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_product_sales",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "pa"],
          "key": "PRIMARY",
          "key_length": "6",
          "used_key_parts": ["category_id", "product_id"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_product_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": ["const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1))"
        }
      },
      {
        "table": {
          "table_name": "cscart_product_descriptions",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "product_id"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["product_id", "lang_code"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_product_descriptions.lang_code = 'en')"
        }
      },
      {
        "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": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["lillyshop_lillyshop.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')"
        }
      },
      {
        "table": {
          "table_name": "cscart_seo_names",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "dispatch"],
          "key": "PRIMARY",
          "key_length": "210",
          "used_key_parts": [
            "object_id",
            "type",
            "dispatch",
            "lang_code",
            "company_id"
          ],
          "ref": ["const", "const", "const", "const", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_seo_names.`type` = 'p' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion",
          "access_type": "const",
          "possible_keys": ["object_id", "idx_company_id"],
          "key": "object_id",
          "key_length": "10",
          "used_key_parts": ["object_id", "object_type", "company_id"],
          "ref": ["const", "const", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion.object_type = 'P')"
        }
      }
    ]
  }
}

Result

product_id	product_code	product_type	status	company_id	list_price	amount	weight	length	width	height	shipping_freight	low_avail_limit	timestamp	updated_timestamp	usergroup_ids	is_edp	edp_shipping	unlimited_download	tracking	free_shipping	zero_price_action	is_pbp	is_op	is_oper	is_returnable	return_period	avail_since	out_of_stock_actions	localization	min_qty	max_qty	qty_step	list_qty_count	tax_ids	age_verification	age_limit	options_type	exceptions_type	details_layout	shipping_params	facebook_obj_type	parent_product_id	yml2_brand	yml2_origin_country	yml2_store	yml2_pickup	yml2_delivery	yml2_adult	yml2_delivery_options	yml2_bid	yml2_model	yml2_sales_notes	yml2_type_prefix	yml2_offer_type	yml2_market_category	yml2_manufacturer_warranty	yml2_expiry	yml2_purchase_price	yml2_description	yml2_cpa	buy_now_url	ab__stickers_manual_ids	ab__stickers_generated_ids	image_extension	fiscal_data_1212	mark_code_type	lang_code	product	shortname	ab__spt_short_name	short_description	full_description	meta_keywords	meta_description	search_words	page_title	age_warning_message	promo_text	ab__custom_product_h1	amp_description	ab__emd_alternative_name	stop_words	price	category_ids	popularity	company_name	sales_amount	seo_name	seo_path	discussion_type
12245	8558	P	A	1	0.00	1	0.000	0	0	0	0.00	0	1617300338	1750228698	0	N	N	N	B	N	R	N	N	N	Y	10	0	N		0	0	0	0		N	0	P	F	default	a:5:{s:16:"min_items_in_box";i:0;s:16:"max_items_in_box";i:0;s:10:"box_length";i:0;s:9:"box_width";i:0;s:10:"box_height";i:0;}		0						N	a:0:{}	0								0		Y				png	1		en	Napkins in a roll Doily 30x40 cm grid, 100 pcs				Disposable napkins in a roll are an ideal option for medical and cosmetology institutions. They play an important role in ensuring a high level of hygiene, which is critical for the health and comfort of both staff and customers. In a number of areas, including cosmetology offices, massage and ultrasound rooms, diagnostic and medical centers, as well as in the restaurant business and public catering, the use of disposable napkins is mandatory and vital for maintaining cleanliness.											165.00000000	398M	4045	LILLYSHOP	31	salfetki-bezvorsovye-6h6-sm-polix-pro-and-med-cherno-belye-400-sht	397/398	B