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

Query time 0.00168

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 = 62623 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": 6,
      "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
62623	BLE-CN-ULM	P	E	C	M	A	A	0	A	1	385.00	1000	0.000	0	0	0	0.00	0	1406865600	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;}	1753285899	Price increased by $38 - DC 5/7/21 37% commercial discount to base price manually added - DC 5/7/21 Price increased by 5% from $305 to $320 - DC 5/26/21 Price increased 10% from $320 to $352 - DC 5/23/22 Price increased from $352 to $385 - DC 10/31/22	Y		none	none	743,744,745	BLE-CN	{"21951":4,"21954":7,"22889":6,"22872":9,"22852":10,"21941":12}	1008383	0	0	0	0	0	0	0	0	1	0.00	0	en	Brisbane Chain Pendant Light			<p>Our popular Brisbane Pendant gets a stylish upgrade with a cord-and-chain mounting! Featuring a shallow shade over a cast guard, you can create both up and downlight with this industrial pendant light. Easy to customize with shade size, finish, plus guard, glass, cap, and cording options, this light can complement any residential decor or commercial brand.<br /><br />American made from commercial-grade materials, the Brisbane Chain Hung Pendant features Quicklink for easy on-site customization. Hang in pairs over the kitchen island or add a nautical touch to the neighborhood restaurant.</p> <p>\|break\|</p> <ul> <li>Manufactured and hand-crafted in the USA</li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>!</li> <li>Read more about the Brisbane Chain Hung Pendant Light over on our<span> <a href="https://www.barnlight.com/inspiration/crikey-industrial-lighting-inspired-by-the-land-down-under/" target="_blank">blog</a></span>!</li> </ul>		Our Brisbane pendant light now sports a chain for the perfect industrial touch! Create up and down light with our newly styled Brisbane today.	the brisbane industrial chain hung pendant, brisbane industrial chain hung pendant, chain hung pendant light, industrial lighting, chain lighting, brisbane, up and down lighting, up and down chain hung light, restaurant lighting, nautical chain lighting, BLE-C-ULMI-PC-CN36, BLE-C-ULMI12-PC-CN36, BLE-C-ULMI14-PC-CN36, BLE-C-ULMI16-PC-CN36, The Brisbane Industrial Chain Hung Pendant, Brisbane Industrial Chain Hung Pendant, The Brisbane Industrial Pendant, The Brisbane Industrial Pendants, Brisbane Industrial Pendant, Brisbane Industrial Pendants, Brisbane industrial pendant, Brisbane industrial pendants, The Brisbane Industrial Pendant, The Brisbane Industrial Pendants, Brisbain, Brisbain industrial pendant, brisbain industrial pendants, Brisbane Industrial Pendant Light, Brisbane Industrial Pendant Lights, Brisbane Industrial Pendant Lighting, BLE-C-ULMI-CN36, Brisbane Chain, Brisbane Chain Light, Brisbane Chain Lights, Brisbane Chain Lighting, BLE-CN-ULM12, BLE-CN-ULM14, BLE-CN-ULM16, Industrial light, industrial lights, industrial lighting, industrial lighting fixture, industrial lighting fixtures, rugged light, rugged lights, rugged lighting, rugged lighting fixture, rugged lighting fixtures, Vintage Industrial light, vintage industrial lights, vintage industrial lighting, vintage industrial lighting fixture, vintage industrial lighting fixtures, cast guard light, cast guard lights, cast guard lighting, cast guard lighting fixture, cast guard lighting fixtures, Queensland collection, queensland lighting, queensland light, queensland lights, queensland lighting, queensland light fixture, queensland light fixtures, Queens land collection, queens land lighting, queens land light, queens land lights, queens land lighting, queens land light fixture, queens land light fixtures, Queensland, Industrial chain hung pendant, industrial chain hung pendants, industrial chain hung pendant light, Industrial chain hung pendant lights, Industrial chain hung pendant lighting, Industrial chain hung ceiling pendant, Industrial chain hung ceiling pendants, Industrial chain hung pendant light, Industrial chain hung pendant lights, Industrial chain hung pendant lighting, Industrial chain hung ceiling light, Industrial chain hung ceiling lights, Industrial chain hung ceiling lighting, Industrial chain pendant, industrial chain pendants, industrial chain pendant light, Industrial chain pendant lights, Industrial chain pendant lighting, Industrial chain ceiling pendant, Industrial chain ceiling pendants, Industrial chain pendant light, Industrial chain pendant lights, Industrial chain pendant lighting, Industrial chain ceiling light, Industrial chain ceiling lights, Industrial chain ceiling lighting, rugged chain hung pendant, rugged chain hung pendants, rugged chain hung pendant light, rugged chain hung pendant lights, rugged chain hung pendant lighting, rugged chain hung ceiling pendant, rugged chain hung ceiling pendants, rugged chain hung pendant light, rugged chain hung pendant lights, rugged chain hung pendant lighting, rugged chain hung ceiling light, rugged chain hung ceiling lights, rugged chain hung ceiling lighting, rugged chain pendant, rugged chain pendants, rugged chain pendant light, rugged chain pendant lights, rugged chain pendant lighting, rugged chain ceiling pendant, rugged chain ceiling pendants, rugged chain pendant light, rugged chain pendant lights, rugged chain pendant lighting, rugged chain ceiling light, rugged chain ceiling lights, rugged chain ceiling lighting, cast guard chain hung pendant, cast guard chain hung pendants, cast guard chain hung pendant light, cast guard chain hung pendant lights, cast guard chain hung pendant lighting, cast guard chain hung ceiling pendant, cast guard chain hung ceiling pendants, cast guard chain hung pendant light, cast guard chain hung pendant lights, cast guard chain hung pendant lighting, cast guard chain hung ceiling light, cast guard chain hung ceiling lights, cast guard chain hung ceiling lighting, cast guard chain pendant, cast guard chain pendants, cast guard chain pendant light, cast guard chain pendant lights, cast guard chain pendant lighting, cast guard chain ceiling pendant, cast guard chain ceiling pendants, cast guard chain pendant light, cast guard chain pendant lights, cast guard chain pendant lighting, cast guard chain ceiling light, cast guard chain ceiling lights, cast guard chain ceiling lighting, Queensland Chain, Queensland Chain Hung, Queensland Chain Hung Light, Queensland Chain Hung Lights, Queensland Chain Hung Lighting, Queensland Chain Hung Pendant, Queensland Chain Hung Pendants, Queensland Chain Hung Pendant Light, Queensland Chain Hung pendant Lights, Queensland Chain Hung Pendant Lighting, Queensland Chain Hung Ceiling Pendant, Queensland Chain Hung Ceiling Pendants, Queensland Chain Hung Ceiling Light, Queensland Chain Hung Ceiling Lights, Queensland Chain Hung Ceiling Lighting, Queens land Chain Hung, Queens land Chain Hung Light, Queens land Chain Hung Lights, Queens land Chain Hung Lighting, Queens land Chain Hung Pendant, Queens land Chain Hung Pendants, Queens land Chain Hung Pendant Light, Queens land Chain Hung pendant Lights, Queens land Chain Hung Pendant Lighting, Queens land Chain Hung Ceiling Pendant, Queens land Chain Hung Ceiling Pendants, Queens land Chain Hung Ceiling Light, Queens land Chain Hung Ceiling Lights, Queens land Chain Hung Ceiling Lighting, industrial pendant, industrial pendants, industrial ceiling pendant, industrial ceiling pendants, industrial ceiling light, industrial ceiling lights, industrial ceiling lighting, rugged pendant, rugged pendants, rugged ceiling pendant, rugged ceiling pendants, rugged ceiling light, rugged ceiling lights, rugged ceiling lighting, cast guard pendant, cast guard pendants, cast guard ceiling pendant, cast guard ceiling pendants, cast guard ceiling light, cast guard ceiling lights, cast guard ceiling lighting, Queensland pendant, Queensland pendants, queensland pendant light, queensland pendant lights, queensland pendant lighting, queensland ceiling pendant, queensland ceiling pendants, queensland ceiling light, queensland ceiling lights, queensland ceiling lighting, Queens land pendant, Queens land pendants, queens land pendant light, queens land pendant lights, queens land pendant lighting, queens land ceiling pendant, queens land ceiling pendants, queens land ceiling light, queens land ceiling lights, queens land ceiling lighting, Brisbane Series, Brisbane Collection, The Brisbane Series, The Brisbane Collection, Australia collection, Australia Series, the Australia collection, the Australia Series, Queensland Collection, Queensland Series, The Queensland Collection, the Queensland Series, Queens land Collection, Queens land Series, The Queens land Collection, the Queens land Series, SPS-0029, SPS 0029, SPS0029, pastel, Chain barn light, Chain barn lights, Chain barn lighting, mint,	Brisbane Chain Pendant Light \| Barn Light Electric				385.000000	1541,1619,1643,1700,2002,1891M	1111326	Barn Light Electric Company	41	brisbane-chain-hung-pendant-light	1887/1888/1891	B