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

Query time 0.00182

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": 1,
      "filtered": 100
    },
    "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": "trigcond(1) and trigcond(cscart_product_prices.product_id = 65659 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_products_categories",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 7,
      "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')"
    },
    "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
65659	BLE-C-BRN	P	E	C	M	D	A	0	A	1	880.00	1000	0.000	0	0	0	0.00	0	1662126589	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;}	1775047107	Price decreased from $950 to $880 - DC 11/8/22	Y		none	none	3576,3577	BLE-C	{"23112":2,"21943":4,"23111":1,"23722":1,"22843":3,"22675":8,"21971":4,"23055":7,"22644":9,"22631":4,"23723":1,"22981":5,"22982":5,"22645":9,"23158":2,"22842":1,"21954":4,"23730":10,"23857":5,"23858":4,"23859":4,"23860":4,"23333":6}	1008383	0	0	0	0	0	0	0	0	1	0.00	0	en	Brunson LED Wooden Top Cord Pendant Light			<div> <div lang="en-US"> <p lang="en-US"><span>With a design that instantly captures attention, the Brunson LED Wooden Top Pendant Light blends contemporary style with timeless aesthetics. The handspun metal shade is fitted to an elongated wooden top then finished with an integrated LED module to create brilliant, energy-efficient illumination. </span><br /><br /><span>Both the metal shade and wooden top demonstrate our full commitment to American-made craftsmanship. The wood is turned on a lathe and carefully worked by hand into the desired shape. Similarly, the metal shade is spun by hand to follow time-honored manufacturing techniques in its construction. With this thoughtful attention-to-detail and a unique style, the Brunson puts the finishing touches on any décor.</span></p> </div> </div> <p>\|break\|</p> <ul> <li>Manufactured in the U.S.A. </li> <li> <div>Part of our exclusive <a title="Learn more about our Timber & Ore Collection" href="/timber-ore-collection/">Timber & Ore Collection</a></div> </li> <li>All wood is sourced from North American suppliers who are committed to sustainable felling and regrowth practices.</li> <li><span>Every p</span><span>iece of wood is unique with slight, natural variations such as knots and grain differences due to natural growth patterns. Therefore, no two pieces will be exactly alike!</span></li> <li><span><span>This light fixture is made-to-order to suit your custom specifications. Learn more about the process </span><a href="https://www.barnlight.com/behind-the-scenes-video-footage/" target="_blank">here</a><span>!</span></span> </li> </ul>		The bold, modern silhouette of the Brunson LED Wooden Top Pendant Light adds a touch of customizable drama and style to a wide variety of spaces.	BLE-C-BRN, BLE-C-BRN-LED, Brunson, the brunson, Bruson Cord, Brunson Cord Pendant, Cord Brunson, Bruson Cord Light, brunson wood, wood pendant, wooden pendant, wood cord pendant, wooden cord pendant, Brunson pendant, wood cord pendant, wooden cord pendant, wood cord light, wooden cord light, timber and ore, timber & ore, timber and ore collection, timber & ore collection, wood lighting, wooden lighting, wood light, wooden light, wood cord lighting, wooden cord lighting, Brunson Wooden Top Pendant, Brunson Wooden Top Pendant Light, Brunson Wood Top Pendant, Bruson Wood top, Bruson wooden top, branson, brunsen, Statement light, statement lights, statement lighting, statement light fixture, statement light fixtures, statement lighting fixture, statement lighting fixtures, State ment light, state ment lights, state ment lighting, state ment light fixture, state ment light fixtures, state ment lighting fixture, state ment lighting fixtures, Statement pendant, statement pendants, statement ceiling pendant, statement ceiling pendants, statement pendant light, statement pendant lights, statement pendant lighting, statement ceiling light, statement ceiling lights, statement ceiling lighting, Decorative light, decorative lights, decorative lighting, decorative light fixture, decorative light fixtures, decorative lighting fixture, decorative lighting fixtures, Decorative pendant, decorative pendants, decorative ceiling pendant, decorative ceiling pendants, decorative pendant light, decorative pendant lights, decorative pendant lighting, decorative ceiling light, decorative ceiling lighting, decorative shade, decorative shades, decorative wood shade, decorative wood shades, decorative wood shade, decorative wooden shade, decorative wood shades, decorative wooden shades, Wood accent light, wood accent lights, wood accent lighting, wood accent light fixture, wood accent light fixtures, wood accent lighting fixture, wood accent lighting fixtures, wood accent, wood accents, Wooden accent light, wooden accent lights, wooden accent lighting, wooden accent light fixture, wooden accent light fixtures, wooden accent lighting fixture, wooden accent lighting fixtures, wooden accent, wooden accents, Wood accent pendant, wood accent pendants, wood accent ceiling pendant, wood accent ceiling pendants, wood accent pendant light, wood accent pendant lights, wood accent pendant lighting, wood accent ceiling light, wood accent ceiling lights, wood accent ceiling lighting, timber & ore collection, the timber & ore collection, timber & ore collection, the timber & ore collection, timber and ore collection, the timber and ore collection, timber and ore collection, the timber and ore collection, timber & ore series, the timber & ore series, timber & ore series, the timber & ore series, timber and ore series, the timber and ore series, timber and ore series, the timber and ore series, Brunson Collection, Brunson Series, SPS-0553, SPS 0553, SPS0553, wood fixtures, wood fixture, wooden fixtures, wooden fixture, wooden hanging light, wooden hanging lights, wooden hanging lighting, wood hanging light, wood hanging lights, wood hanging lighting, corded pendant, corded pendants, corded pendant light, corded pendant lights, corded pendant lighting, corded ceiling pendant, corded ceiling pendants, corded ceiling light, corded ceiling lights, corded ceiling lighting, corded pendant light fixture, corded pendant light fixtures, corded pendant lighting fixture, corded pendant lighting fixtures, corded light fixture, corded light fixtures, corded lighting fixture, corded lighting fixtures, corded, wooden top rlm, wood and metal, wooden top barn light, wood lights, wood and metal lights, 23NDP1023, timber ore, led timber ore, led wood, wood led, timber and ore led, timber ore led, led timber and ore, led timber ore, wood accent light, wood accent pendant,	Brunson LED Wooden Top Cord Pendant Light \| Barn Light Electric				880.000000	1844,1850,1922,1928,1983,2002,1985M	51655	Barn Light Electric Company	19	brunson-led-wooden-top-pendant	1920/1921/1985	B