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

Query time 0.00128

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 = 62420 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
62420	BLE-F-ULM-LED	P	E	C	M	W	D	0	A	1	495.00	1000	0.000	0	0	0	0.00	0	1401681600	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;}	1753285852	Price increased 5% from $383 to $402 - DC 6/1/21 Price decreased from $402 to $369 - DC 12/10/21 Price increased 10% from $369 to $406 - DC 6/2/22 Price increased from $406 to $495 - DC 10/31/22	Y		none	none	1641,1642,1643	BLE-F	{"23116":12,"22644":13,"22869":8,"22870":8,"22871":8,"22872":9,"22852":10}	1008383	0	0	0	1	0	0	0	0	1	0.00	0	en	Brisbane LED Flush Mount Pendant Light			<p>Tight areas and rooms with low ceilings have their own spatial requirements, and with its compact flush mounting, the Brisbane offers the proper dimensions for most settings. While this pendant emphasizes performance, it still demonstrates an eye-catching decorative appeal. An industrial guard and simple shade establish a look that adheres to vintage styles but remains relevant to modern spaces. With this touch of rugged character, even the smallest rooms can feel stylish, welcoming, and unique.</p> <p>Integrated LED components help the Brisbane produce illumination efficiently and further its functional purposes. By reducing the amount of electricity needed to brighten a space, this technology is responsible for lower power bills and greater environmental sustainability. Save energy, help out the environment, and transform any home or business with the Brisbane!</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><span><span class="ui-provider a b c d e f g h i j k l m n o p q r s t u v w x y z ab ac ae af ag ah ai aj ak" dir="ltr">Please note: Canopies are unavailable in copper and brass. If copper or brass is selected for the "Shade and Canopy" dropdown, canopy will match the guard finish selected. </span></span></li> </ul>	led flush mount pendant, led flushmount light, downlighting	Illuminate rooms with low ceilings with this industrial LED flush mount pendant. This LED flush mount light provides up/downlighting thanks to its shallow bowl shade and guard & glass style.	Brisbane LED Flush Mount Pendant, BLE-F-ULM-PC-LED, brisbane led flush, brisbane flush led, brisbane led semi flush, semi flush led, led rustic flush, rustic led semi flush, industrial led flush mount, industrial flush, flush led, led flush, led flush mount, flush mount led, led flush pendant, flush pendant, brisbane led, led brisbane, industrial led flush, industrial flush led, BLE-F-ULM12, BLE-F-ULM14, BLE-F-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 flush, industrial flush mount, industrial flush mounts, industrial flush mount pendant, industrial flush mount pendants, industrial flush mount ceiling pendant, industrial flush mount ceiling pendants, industrial flush mount pendant light, industrial flush mount pendant lights, industrial flush mount pendant lighting, industrial flush mount ceiling light, industrial flush mount ceiling lights, industrial flush mount ceiling lighting, Rugged flush, rugged flush mount, rugged flush mounts, rugged flush mount pendant, rugged flush mount pendants, rugged flush mount ceiling pendant, rugged flush mount ceiling pendants, rugged flush mount pendant light, rugged flush mount pendant lights, rugged flush mount pendant lighting, rugged flush mount ceiling light, rugged flush mount ceiling lights, rugged flush mount ceiling lighting, Cast guard flush, cast guard flush mount, cast guard flush mounts, cast guard flush mount pendant, cast guard flush mount pendants, cast guard flush mount ceiling pendant, cast guard flush mount ceiling pendants, cast guard flush mount pendant light, cast guard flush mount pendant lights, cast guard flush mount pendant lighting, cast guard flush mount ceiling light, cast guard flush mount ceiling lights, cast guard flush mount ceiling lighting, Queensland Flush mount, Queensland Flush mount Light, Queensland Flush mount Lights, Queensland Flush mount Lighting, Queensland Flush mount Pendant, Queensland Flush mount Pendants, Queensland Flush mount Pendant Light, Queensland Flush mount pendant Lights, Queensland Flush mount Pendant Lighting, Queensland Flush mount Ceiling Pendant, Queensland Flush mount Ceiling Pendants, Queensland Flush mount Ceiling Light, Queensland Flush mount Ceiling Lights, Queensland Flush mount Ceiling Lighting, Queens land Flush mount , Queens land Flush mounts, Queens land Flush mount Light, Queens land Flush mount Lights, Queens land Flush mount Lighting, Queens land Flush mount Pendant, Queens land Flush mount Pendants, Queens land Flush mount Pendant Light, Queens land Flush mount pendant Lights, Queens land Flush mount Pendant Lighting, Queens land Flush mount Ceiling Pendant, Queens land Flush mount Ceiling Pendants, Queens land Flush mount Ceiling Light, Queens land Flush mount Ceiling Lights, Queens land Flush mount 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, LED Industrial light, LED industrial lights, LED industrial lighting, LED industrial lighting fixture, LED industrial lighting fixtures, LED rugged light, LED rugged lights, LED rugged lighting, LED rugged lighting fixture, LED rugged lighting fixtures, LED Vintage Industrial light, LED vintage industrial lights, LED vintage industrial lighting, LED vintage industrial lighting fixture, LED vintage industrial lighting fixtures, LED cast guard light, LED cast guard lights, LED cast guard lighting, LED cast guard lighting fixture, LED cast guard lighting fixtures, LED metropolis, LED metropolis collection, LED metropolis light, LED metropolis lights, LED metropolis lighting, Industrial LED light, industrial LED lights, industrial LED lighting, industrial LED lighting fixture, industrial LED lighting fixtures, rugged LED light, rugged LED lights, rugged LED lighting, rugged LED lighting fixture, rugged LED lighting fixtures, Vintage Industrial LED light, vintage industrial LED lights, vintage industrial LED lighting, vintage industrial LED lighting fixture, vintage industrial LED lighting fixtures, cast guard LED light, cast guard LED lights, cast guard LED lighting, cast guard LED lighting fixture, cast guard LED lighting fixtures, Queensland LED, Queens Land LED, LED Queensland, LED Queens Land, LED Industrial flush mount pendant, LED industrial flush mount pendants, LED industrial flush mount pendant light, LED Industrial flush mount pendant lights, LED Industrial flush mount pendant lighting, LED Industrial flush mount ceiling pendant, LED Industrial flush mount ceiling pendants, LED Industrial flush mount pendant light, LED Industrial flush mount pendant lights, LED Industrial flush mount pendant lighting, LED Industrial flush mount ceiling light, LED Industrial flush mount ceiling lights, LED Industrial flush mount ceiling lighting, LED Industrial flush pendant, LED industrial flush pendants, LED industrial flush pendant light, LED Industrial flush pendant lights, LED Industrial flush pendant lighting, LED Industrial flush ceiling pendant, LED Industrial flush ceiling pendants, LED Industrial flush pendant light, LED Industrial flush pendant lights, LED Industrial flush pendant lighting, LED Industrial flush ceiling light, LED Industrial flush ceiling lights, LED Industrial flush ceiling lighting, Industrial LED flush mount pendant, industrial LED flush mount pendants, industrial LED flush mount pendant light, Industrial LED flush mount pendant lights, Industrial LED flush mount pendant lighting, Industrial LED flush mount ceiling pendant, Industrial flush LED hung ceiling pendants, Industrial LED flush mount pendant light, Industrial LED flush mount pendant lights, Industrial LED flush mount pendant lighting, Industrial LED flush mount ceiling light, Industrial LED flush mount ceiling lights, Industrial LED flush mount ceiling lighting, Industrial LED flush pendant, industrial LED flush pendants, industrial LED flush pendant light, Industrial LED flush pendant lights, Industrial LED flush pendant lighting, Industrial LED flush ceiling pendant, Industrial LED flush ceiling pendants, Industrial LED flush pendant light, Industrial LED flush pendant lights, Industrial LED flush pendant lighting, Industrial LED flush ceiling light, Industrial LED flush ceiling lights, Industrial LED flush ceiling lighting, LED rugged flush mount pendant, LED rugged flush mount pendants, LED rugged flush mount pendant light, LED rugged flush mount pendant lights, LED rugged flush mount pendant lighting, LED rugged flush mount ceiling pendant, LED rugged flush mount ceiling pendants, LED rugged flush mount pendant light, LED rugged flush mount pendant lights, LED rugged flush mount pendant lighting, LED rugged flush mount ceiling light, LED rugged flush mount ceiling lights, LED rugged flush mount ceiling lighting, LED rugged flush pendant, LED rugged flush pendants, LED rugged flush pendant light, LED rugged flush pendant lights, LED rugged flush pendant lighting, LED rugged flush ceiling pendant, LED rugged flush ceiling pendants, LED rugged flush pendant light, LED rugged flush pendant lights, LED rugged flush pendant lighting, LED rugged flush ceiling light, LED rugged flush ceiling lights, LED rugged flush ceiling lighting, rugged LED flush mount pendant, rugged LED flush mount pendants, rugged LED flush mount pendant light, rugged LED flush mount pendant lights, rugged LED flush mount pendant lighting, rugged LED flush mount ceiling pendant, rugged LED flush mount ceiling pendants, rugged LED flush mount pendant light, rugged LED flush mount pendant lights, rugged LED flush mount pendant lighting, rugged LED flush mount ceiling light, rugged LED flush mount ceiling lights, rugged LED flush mount ceiling lighting, rugged LED flush pendant, rugged LED flush pendants, rugged LED flush pendant light, rugged LED flush pendant lights, rugged LED flush pendant lighting, rugged LED flush ceiling pendant, rugged LED flush ceiling pendants, rugged LED flush pendant light, rugged LED flush pendant lights, rugged LED flush pendant lighting, rugged LED flush ceiling light, rugged LED flush ceiling lights, rugged LED flush ceiling lighting, LED cast guard flush mount pendant, LED cast guard flush mount pendants, LED cast guard flush mount pendant light, LED cast guard flush mount pendant lights, LED cast guard flush mount pendant lighting, LED cast guard flush mount ceiling pendant, LED cast guard flush mount ceiling pendants, LED cast guard flush mount pendant light, LED cast guard flush mount pendant lights, LED cast guard flush mount pendant lighting, LED cast guard flush mount ceiling light, LED cast guard flush mount ceiling lights, LED cast guard flush mount ceiling lighting, LED cast guard flush pendant, LED cast guard flush pendants, LED cast guard flush pendant light, LED cast guard flush pendant lights, LED cast guard flush pendant lighting, LED cast guard flush ceiling pendant, LED cast guard flush ceiling pendants, LED cast guard flush pendant light, LED cast guard flush pendant lights, LED cast guard flush pendant lighting, LED cast guard flush ceiling light, LED cast guard flush ceiling lights, LED cast guard flush ceiling lighting, cast guard LED flush mount pendant, cast guard LED flush mount pendants, cast guard LED flush mount pendant light, cast guard LED flush mount pendant lights, cast guard LED flush mount pendant lighting, cast guard LED flush mount ceiling pendant, cast guard LED flush mount ceiling pendants, cast guard LED flush mount pendant light, cast guard LED flush mount pendant lights, cast guard LED flush mount pendant lighting, cast guard LED flush mount ceiling light, cast guard LED flush mount ceiling lights, cast guard LED flush mount ceiling lighting, cast guard LED flush pendant, cast guard LED flush pendants, cast guard LED flush pendant light, cast guard LED flush pendant lights, cast guard LED flush pendant lighting, cast guard LED flush ceiling pendant, cast guard LED flush ceiling pendants, cast guard LED flush pendant light, cast guard LED flush pendant lights, cast guard LED flush pendant lighting, cast guard LED flush ceiling light, cast guard LED flush ceiling lights, cast guard LED flush ceiling lighting, flush led guard, 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, Outside flush mount, Outside flush mounts, Outside flush mount light, Outside flush mount lights, Outside flush mount lighting, Outside flush mount light fixture, Outside flush mount light fixtures, Outside flush mount lighting fixture, Outside flush mount lighting fixtures, Outside flush mount pendant, Outside flush mount pendants, Outside pendant, Outside pendants, Outside ceiling pendant, Outside ceiling pendants, Outside pendant light, Outside pendant lights, Outside pendant lighting, Outside ceiling pendant, Outside ceiling pendants, Outside lights, Outside light, Outside lighting, Out side lights, Out side light, Out side lighting, barn light for outside, barn lights for outside, ceiling flush mount,	Brisbane LED Flush Mount Pendant Light \| Barn Light Electric				495.000000	1609,1619,1853,1856,1931,1934,2001,1608M	300349	Barn Light Electric Company	44	brisbane-led-flush-mount-pendant-light	1516/1529/1584/1608	B