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

Query time 0.00121

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 = 61620 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": 9,
      "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
61620	BLE-W-SCH10-24VAN	P	E	C	M	A	A	0	A	1	849.00	1000	0.000	0	0	0	0.00	0	1365998400	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;}	1753110168	Price increased 5% from $734 to $771 - DC 5/24/21 Price increased 10% from $771 to $849 - DC 5/31/22	Y		none	none	682,683,684,689	BLE-W	{"22821":1,"22818":2,"22796":3,"22794":3,"22798":4,"22397":5}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Primary 2-Light Schoolhouse Vanity Light			<p>Taking cues from vintage designs, this schoolhouse vanity light embraces simple, timeless lines and a classic profile. To create this look, two glass globes are joined to a single backing plate. Immediately elevating any room's aesthetic, the Primary feels at home in both refined modern spaces and more casual décors.</p> <p>This vanity light is fully customizable to match any space. Select from several stripe patterns, finish options, and glass tints and create a truly unique look for your projects.</p> <p>\|break\|</p> <ul> <li>Features two customizable glass schoolhouse globes with hand-spun fitters.</li> <li><span>White painted band(s) not recommended if opaque glass is selected, as it blends into the glass.</span></li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>!</li> </ul>	double wall sconce, vintage vanity light, schoolhouse lighting, american made schoolhouse light, glass globe wall sconce with colorful band, school house sconce, bath vanity light, bronze schoolhouse light	Inspired by vintage lights, add Primary Schoolhouse Wall Light to your home. Accent yours with colorful bands for an instantly classic bathroom vanity light!	Collegiate 2-Light Schoolhouse Vanity Light, BLE-W-SCH10-24VAN, 2-light schoolhouse sconce, schoolhouse sconce light, schoolhouse lighting, schoolhouse wall light, wall lights, vanity lights, vanity lighting, vanity wall sconce, bathroom lighting, bathroom wall lights, 2-light vanity light, 2-light wall sconce, 2-light wall light, Freshman, Freshman schoolhouse, freshman schoolhouse light, freshman schoolhouse vanity light, Primary, Primary Schoolhouse, Primary School house, Primary schoolhouse shade, primary school house shade, primary schoolhouse light, primary school house light, Primary Schoolhouse Sconce, Primary Schoolhouse Wall Sconce, Primary School house Sconce, Primary School house Wall Sconce, Primary Schoolhouse Sconce Light, Primary School house Sconce Light, Primary Sconce, Primary Sconces, Primary Sconce Light, Primary 2-Light Schoolhouse Vanity Light, Primary 2 Light Schoolhouse Vanity Light, Primary two Light Schoolhouse Vanity Light, Primary 2-Light Schoolhouse Vanity sconce, Primary 2-Light Schoolhouse Vanity Wall Sconce, Primary Schoolhouse Vanity Light, Primary Schoolhouse Vanity Sconce, Primary Schoolhouse Vanity Wall Sconce, Primary Vanity Light, Primary Vanity Sconce, Primary Vanity Wall Sconce, wall vanity, wall vanity light, schoolhouse, school house, schoolhouse sconce, schoolhouse wall sconce, school house sconce, school house wall sconce, schoolhouse vanity sconce, school house vanity sconce, schoolhouse vanity wall sconce, school house vanity wall sconce, primary schoolhouse, primary school house, primary, glass lighting, glass lighting fixture, glass lighting fixtures, glass shade, glass shades, glass shade light, glass shade lights, glass shade lighting, glass globe, glass globes, glass globe shade, glass globe shades, glass globe light, glass globe lighting, glass globe shade light, glass globe shade lights, glass globe shade lighting, glass light fixture, glass light fixtures, glass wall lighting, glass wall lighting fixture, glass wall lighting fixtures, glass shade wall light, glass shade wall lights, glass shade wall lighting, glass globe wall light, glass globe wall lighting, glass globe wall lights, glass globe shade wall light, glass globe shade wall lights, glass globe shade wall lighting, glass wall light fixture, glass wall light fixtures, glass sconce, glass sconce fixture, glass sconce fixtures, glass wall sconce, glass wall sconce fixture, glass wall sconce fixtures, glass shade sconce, glass shade wall sconce, glass sconce light, glass shade sconce light, glass globe sconce, glass globe wall sconce, glass globe wall sconces, glass globe sconce light, glass shade sconce, glass shade sconces, glass shade wall sconce, glass shade wall sconces, glass shade sconce light, BLE-W-SCH10-24VAN, BLE-W-SCHCL10-24VAN, Vanity lightdouble sconce, Vanity light double sconce, vanity, Bathroom vanity light fixture, Bathroom vanity light fixtures, Bathroom vanity fixture, Bathroom vanity fixtures, Bathroom vanity light, Bathroom vanity lights, Bathroom vanity lighting, Bath vanity light, Bath vanity lights, Bath vanity lighting, Bath vanity fixture, Bath vanity fixtures, bathroom light fixtures, bathroom light fixture, bathroom lighting fixtures, bathroom lighting fixture, bath light fixtures, bath light fixture, bath lighting fixtures, bath lighting fixture, Wall mount light, wall mount lights, wall mount lighting, Wall mount light fixture, Wall mount light fixtures, Wall mount lighting fixture, Wall mount lighting fixtures, wall mounted light, wall mounted lights, wall mounted lighting, wall mounted light fixture, wall mounted light fixtures, wall mounted lighting fixture, wall mounted lighting fixtures, Wall mount sconce light, Wall mount sconce lights, Wall mount sconce lighting, Wall mount sconce, Wall mount sconces, Wall mounted sconce light, Wall mounted sconce lights, Wall mounted sconce lighting, Wall mounted sconce, Wall mounted sconces, 2 light school house, 2 light schoolhouse, double light wall sconces, vanity plates, striped schoolhouse, striped schoolhouse light,	Primary 2-Light Schoolhouse Vanity Light \| Barn Light Electric				849.000000	1531,1547,1756,1808,1827,1834,1883,2002,1902M	288912	Barn Light Electric Company	28	primary-2-light-schoolhouse-vanity-light	1887/1896/1902	B