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(
    IF(
      cscart_products_categories.link_type = 'M', 
      CONCAT(
        cscart_products_categories.category_id, 
        'M'
      ), 
      cscart_products_categories.category_id
    )
  ) 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.company_id = 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 = 2010 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 61444 
  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' 
WHERE 
  cscart_products.product_id = 61444 
  AND (
    companies.status = 'A' 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00117

JSON explain

{
  "query_block": {
    "select_id": 1,
    "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": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["company_id"],
      "ref": ["const"],
      "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": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "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,
      "using_index": true
    },
    "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": ["cscart_migrate.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "cscart_categories.company_id = 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')"
    },
    "block-nl-join": {
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
        "key": "product_id",
        "key_length": "9",
        "used_key_parts": ["product_id", "usergroup_id", "lower_limit"],
        "rowid_filter": {
          "range": {
            "key": "lower_limit",
            "used_key_parts": ["lower_limit", "product_id", "usergroup_id"]
          },
          "rows": 2,
          "selectivity_pct": 0.074128984
        },
        "rows": 2,
        "filtered": 100,
        "attached_condition": "cscart_product_prices.product_id = 61444 and cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1)"
      },
      "buffer_type": "flat",
      "buffer_size": "8Kb",
      "join_type": "BNL",
      "attached_condition": "trigcond(1) and trigcond(cscart_product_prices.product_id = 61444 and 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": "product_id",
      "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_seo_names",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "dispatch"],
      "key": "dispatch",
      "key_length": "210",
      "used_key_parts": [
        "dispatch",
        "object_id",
        "type",
        "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": "ref",
      "possible_keys": ["object_id"],
      "key": "object_id",
      "key_length": "6",
      "used_key_parts": ["object_id", "object_type"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_discussion.object_type = 'P')"
    }
  }
}

Result

product_id	product_code	product_type	trustmark_origin	trustmark_certified	trustmark_leadtime	trustmark_location	trustmark_return	owner_id	status	company_id	list_price	amount	weight	length	width	height	shipping_freight	low_avail_limit	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	updated_timestamp	pricing_note	use_new_layout	category_image	tax_code	upc_code	product_detail_ids	nav_sku	sku_columns	nav_review_user_id	hide_price	vendor_fulfilled	hide_commercial_price	non_returnable	ble_basics	commercial_hidden	allow_configurator	enable_customize	enable_buy_american	base_cost	redline_p	lang_code	product	shortname	short_description	full_description	meta_keywords	meta_description	search_words	page_title	age_warning_message	internal	promo_text	price	category_ids	popularity	company_name	sales_amount	seo_name	seo_path	discussion_type
61444	BLE-S-CGG3	P	E	C	M	W	A	0	A	1	700.00	1000	0.000	0	0	0	0.00	0	1359954000	0	N	N	N	D	N	R	N	N	N	Y	10	0	N		0	0	0	0	13	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;}	1779997847	Price decreased by $58 - DC 3/26/20 Price increased 5% from $525 to $551 - DC 5/28/21 Price increased by $106 - DC 6/16/21 Price increased 10% from $657 to $723 - DC 5/24/22 Price decreased from $723 to $700 - DC 11/4/22	Y	images/category_images/alliance-navy-more-styles-available.jpg	none	none	1099,1100	BLE-S-CGG3	{"23050":1,"22394":2,"23042":4,"23015":5,"23016":6,"23026":7,"23025":7,"23024":7,"22396":8,"21970":1,"24042":6}	1008383	0	0	0	0	0	0	0	0	1	0.00	0	en	Alliance 3-Light Chandelier			<p>Ceiling lighting with plenty of nautical flair, the Alliance 3-Light Chandelier features three cast guard and glass fixtures, each attached to a pipe mounting. Suiting both modern and traditional décors, the minimalist look of this ceiling light is incredibly versatile. The Alliance can be found in farmhouse-inspired settings, industrial homes and businesses, and places with a nautical vibe.</p> <p>You get to decide the Alliance’s look. Add eye-catching shades or leave them off for a more stripped-down style. Opt for the raw look of galvanized metal or select a powdercoat finish color to match this rustic chandelier to your décor. Whichever you choose, the Alliance 3-Light Chandelier will transform your home or business.</p> <p>\|break\|</p> <ul> <li>This chandelier features three die casted guards and customizable glass!</li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>.</li> <li>See the Alliance 3-Light Chandelier in a beautiful kitchen over on our <a href="https://www.barnlight.com/inspiration/pros-corner-qa-on-design-lighting-with-d2-interieurs/" target="_blank">blog</a>!</li> </ul>	industrial farmhouse chandelier light, down light chandelier, cast guard and glass three light chandelier, cluster pendant light, contemporary lighting chandeliers, nautical chandelier,	The Alliance 3-light chandelier is full of nautical flair with its industrial pendant style mount and cast guard and glass fixtures that reflect ample lighting.	The Alliance, three light chandelier, 3-light chandelier, BLE-S-CGG3, rustic chandelier, down light, cast guard and glass chandelier, CGG, CGU, TGG, industrial chandelier, industrial chandelier lighting, industrial ceiling light, cast guard and glass, cast guard and glass lighting, cast guard and glass ceiling light, The Alliance 3 Light Chandelier, The Alliance 3-Light Chandelier, The Alliance cast guard and glass, The Alliance cast guard lighting, the alliance ceiling lights, the alliance ceiling chandelier, industrial chandelier, industrial ceiling light, The Alliance 3-Light Chandelier, The Chappel 3-Light Chandelier, The Coalition 3-Light Chandelier, Rugged guard light, rugged guard lights, rugged guard lighting, rugged guard light fixture, rugged guard lighting fixture, rugged guard light fixtures, rugged guard lighting fixtures, rugged guard pendant, rugged guard pendant lights, rugged guard ceiling pendant, rugged guard ceiling pendants, rugged guard ceiling light, rugged guard ceiling lights, rugged guard ceiling lighting, rugged guard chandelier, rugged guard chandeliers, nautical chandelier, nautical chandeliers, SPS-0236, SPS0236, SPS 0236, cast guard collection, cast guard series, rugged guard collection, rugged guard series, industrial guard collection, industrial guard series, cgg collection, cgg series, alliance chandelier, alliance chandeliers, alliance 3-light chandelier, alliance 3-light chandeliers, alliance 3 light chandelier, alliance three light chandelier, ble-c-gg	Alliance 3-Light Chandelier \| Barn Light Electric				700.000000	2010M	455690	Barn Light Electric Company		alliance-3-light-chandelier	1887/1888/2010	B