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 = 959 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 20768 
  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 = 20768 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00181

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
20768	5911	P	A	1	0.00	0	0.000	0	0	0	0.00	0	1684847034	1746085065	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				jpg	1		en	Carbide cone milling cutter DARK No. 207 blue				We present you the Dark carbide cutter with a cone-shaped shape, model #207. This tool is ideal for various manicure and pedicure procedures. Due to its shape, the milling cutter provides: - effective sawing of material from under the nail, eliminating peeling, - removal of acrylic and gel coating, - treatment of calluses and corns. Use it for hardware manicure and pedicure with maximum comfort and effectiveness. This cutter meets all requirements of sterility and hygiene, resistant to any disinfection and sterilization methods, making it a safe and reliable tool in your work. Choose quality and efficiency with the Dark #207 cutter for the best results in your work!											275.00000000	959M	3836	LILLYSHOP	24	nasadka-tverdosplavna-dark-207-sinya-en	443/444/640/959	D