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 = 65656 
  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 = 65656 
  AND (
    companies.status = 'A' 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00122

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 = 65656 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": 8,
      "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
65656	BLE-C-WFCC	P	E	C	M	D	A	0	A	1	725.00	1000	0.000	0	0	0	0.00	0	1661971649	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;}	1769801218		Y		none	none	3568,3569	BLE-C	{"23112":2,"21943":4,"23111":1,"23722":1,"22843":3,"22675":7,"21971":4,"23055":6,"22644":8,"22631":4,"23723":1,"22981":5,"22982":5,"22645":9,"23158":2,"21954":4,"23730":9,"23857":4,"23858":4,"23859":4,"23860":4}	1008383	0	0	0	0	0	0	0	0	1	0.00	0	en	Conoco LED Wooden Top Cord Pendant Light			<div> <div lang="en-US"> <p lang="en-US">With its sleek modern shade topped by a rustic piece of wood, the Conoco LED Wooden Top Pendant Light <span>balances contemporary appeal with vintage style. Both the shade and wooden top are hand spun on a lathe by our talented artisans right here in America. This fixture is easy to customize with a variety of wood species, finish colors, cording, and other options to choose from. </span></p> <p lang="en-US"><span>Add in the very latest in LED technology and you have a pendant light that is both stylish and energy efficient. Suitable for both residential and commercial settings, this pendant is an environmentally friendly fixture that will illuminate your spaces for years to come!</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>		An understated shallow RLM shade is brought to life with artisanal wood, creating a balance of industrial and boho style that works in a variety of spaces!	timber, ore, timber and ore, timber & ore, Conoco Draftsman Wooden Top Pendant, Conoco Draftsman Wooden Top Pendant light, Conoco Wooden Top Pendant, Conoco Wooden Top Pendant light, Conoco Draftsman Wooden Pendant, Conoco Draftsman Wooden Pendant light, Conoco Wooden Pendant, Conoco Wooden Pendant light, Conoco Draftsman Wood Top Pendant, Conoco Draftsman Wood Top Pendant light, Conoco Wood Top Pendant, Conoco Wood Top Pendant light, Conoco Draftsman Wood Pendant, Conoco Draftsman Wood Pendant light, Conoco Wood Pendant, Conoco Wood Pendant light, Draftsman Wooden Top Pendant, Draftsman Wooden Top Pendant light, Draftsman Wooden Pendant, Draftsman Wooden Pendant light, Draftsman Wood Top Pendant, Draftsman Wood Top Pendant light, Draftsman Wood Pendant, Draftsman Wood Pendant light, Conoco Draftsman Pendant, Conoco Draftsman Pendant Light, Conoco Pendant, Conoco Pendant Light, Conoco Draftsman Wooden Top Ceiling Pendant, Conoco Draftsman Wooden Ceiling Pendant, Conoco Draftsman Wood Top Ceiling Pendant, Conoco Draftsman Wood Ceiling Pendant, Conoco Draftsman Wooden Top Ceiling Pendants, Conoco Draftsman Wooden Ceiling Pendants, Conoco Draftsman Wood Top Ceiling Pendants, Conoco Draftsman Wood Ceiling Pendants, Conoco Wooden Top Ceiling Pendant, Conoco Wooden Ceiling Pendant, Conoco Wood Top Ceiling Pendant, Conoco Wood Ceiling Pendant, Conoco Wooden Top Ceiling Pendants, Conoco Wooden Ceiling Pendants, Conoco Wood Top Ceiling Pendants, Conoco Wood Ceiling Pendants, Draftsman Wooden Top Ceiling Pendant, Draftsman Wooden Ceiling Pendant, Draftsman Wood Top Ceiling Pendant, Draftsman Wood Ceiling Pendant, Draftsman Wooden Top Ceiling Pendants, Draftsman Wooden Ceiling Pendants, Draftsman Wood Top Ceiling Pendants, Draftsman Wood Ceiling Pendants, Conocco, Conoco Pendant, Conocco Pendant, Conoco Pendant, Conoco Pendants, Draftsman, Drafts man, Timber & Ore, Timber and ore, timber & ore collection, Timber and ore collection, timber & ore light, timber & ore fixture, timber and ore light, timber and ore fixture, Downtown, down town, minimalist, downtown minimalist, down town minimalist, multi-light minimalist pendant, multi light minimalist pendant, wood, wooden, wood pendant, wooden pendant, conoco, conoco shade, conoco shades, wood conoco, conoco wood, Light with wooden top, light with wood top, light with wooden fixture, light with wood fixture, light with wood top, light with wooden top, light with wood, light with wood socket, light with wooden socket, light with wooden sockets, light with wood sockets, wood socket, wooden sockets, wood socket light, wooden socket lights, Pendant with wooden top, pendant with wood top, Pendant with wooden fixture, pendant with wood fixture, pendant with wood top, pendant with wooden top, pendant with wood, pendant with wood socket, pendant with wooden socket, pendant with wooden sockets, pendant with wood sockets, Pendants with wooden top, pendants with wood top, Pendants with wooden fixture, pendants with wood fixture, pendants with wood top, pendants with wooden top, pendants with wood, pendants with wood socket, pendants with wooden socket, pendants with wooden sockets, pendants with wood sockets, BLE-C-WFCC10, BLE-C-WFCC12, BLE-C-WFCC15, SPS-0497, SPS 0497, SPS0497, 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 barn light, wood lights, wood and metal lights, timber ore, wood led, led wood, timber and ore led, timber ore led, led timber and ore, led timber ore, wood accent light, wood accent pendant,	Conoco LED Wooden Top Cord Pendant Light \| Barn Light Electric				725.000000	1542,1844,1850,1922,1928,1983,2002,1985M	61905	Barn Light Electric Company	12	conoco-led-wooden-top-pendant-light	1920/1921/1985	B