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

Query time 0.00145

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 = 64468 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": 10,
      "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
64468	BLE-S-SCH-LED	P	E	C	M	A	D	0	A	1	765.00	1000	0.000	0	0	0	0.00	0	1524492009	0	N	N	N	D	N	P	N	N	N	Y	10	1524456000	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;}	1757953108	Price increased 5% from $662 to $695 - DC 6/2/21 Price increased 10% from $695 to $765 - DC 6/2/22	Y		none	none	1674,1675,1676	BLE-S	{"23181":3}	1056013	0	0	0	1	0	0	0	0	1	0.00	0	en	Primary Schoolhouse High Lumen LED Stem Pendant Light			<p>While other fixtures used for primary lighting in commercial settings lack a sense of style, this high lumen light adopts a classic look. To create this innovative design, a cutting-edge LED module is concealed behind a timeless schoolhouse shade and affixed to a durable stem mounting. The resulting LED pendant produces an extremely bright light — up to 6,500 lumens — and contributes to a venue’s overall décor. The LED module controls the overall feel of the light with several color temperature options.</p> <p>Each high lumen light is constructed and assembled in our Florida-based manufacturing facility, demonstrating our sincere commitment to quality. These pendants can take on a business’s preferred style with colorful finishes and optional stripes for the schoolhouse shade!</p> <p>\|break\|</p> <ul> <li>Features a customizable glass schoolhouse globe and hand-spun fitter.</li> <li>This light is made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>.</li> <li>White painted band(s) not recommended if opaque glass is selected, as it blends into the glass.</li> <li><span>The single band style has a silver border outlining the chosen finish.</span></li> </ul>		Stylish, high performance LED lighting! A stem mount light with high lumen LED module and timeless schoolhouse style! Fully customizable and American made!	BLE-S-SCH-LED, The Schoolhouse High Lumen LED Stem Mount Light, The Schoolhouse High Lumen LED Stem Mount Lights, The Schoolhouse High Lumen LED Stem Mount Lighting, Schoolhouse High Lumen LED Stem Mount Light, Schoolhouse High Lumen LED Stem Mount Lights, Schoolhouse High Lumen LED Stem Mount Lighting, The Schoolhouse High Lumen LED Stem Light, The Schoolhouse High Lumen LED Stem Lights, The Schoolhouse High Lumen LED Stem Lighting, Schoolhouse High Lumen LED Stem Light, Schoolhouse High Lumen LED Stem Lights, Schoolhouse High Lumen LED Stem Lighting, High Lumen LED Stem Mount Light, High Lumen LED Stem Mount Lights, High Lumen LED Stem Mount Lighting, High Lumen LED Stem Light, High Lumen LED Stem Lights, High Lumen LED Stem Lighting, High lumen led light, high lumen led lights, high lumen led lighting, high lumen light, high lumen lights, high lumen lighting, High lumen schoolhouse, high lumen pendant, high lumen pendants, high lumen school house, led, leds, high lumen, Primary Schoolhouse High Lumen LED Stem Mount Light, Primary High Lumen LED Stem Mount Light, Primary Schoolhouse High-Lumen LED Stem Mount Light, Primary Schoolhouse High Lumen Stem Mount Light, Primary High Lumen Stem Mount Light, Primary High-Lumen Stem Mount Light, Primary Schoolhouse High Lumen LED Stem Mount, Primary High Lumen LED Stem Mount, Primary Schoolhouse High-Lumen LED Stem Mount, Primary Schoolhouse High Lumen Stem Mount, Primary High Lumen Stem Mount, Primary High-Lumen Stem Mount, Primary Schoolhouse High Lumen LED Light, Primary High Lumen LED Light, Primary Schoolhouse High-Lumen LED Light, Primary Schoolhouse High Lumen Light, Primary High Lumen Light, Primary High-Lumen Light, high lumen, high-lumen, 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 pendant light, glass pendant lights, glass pendant lighting, glass pendant light fixture, glass pendant light fixtures, glass pendant lighting fixture, glass pendant lighting fixtures, glass shade pendant light, glass shade pendant lights, glass shade pendant lighting, glass globe pendant light, glass globe pendant lighting, glass globe pendant lighting, glass globe pendant lights, glass globe shade pendant light, glass globe shade pendant lights, glass globe shade pendant lighting, glass ceiling light, glass ceiling lights, glass ceiling lighting, glass ceiling lighting fixture, glass ceiling lighting fixtures, glass shade ceiling light, glass shade ceiling lights, glass shade ceiling lighting, glass globe ceiling light, glass globe ceiling lighting, glass globe shade ceiling light, glass globe shade ceiling lights, glass globe shade ceiling lighting, glass ceiling light fixture, glass ceiling light fixtures, glass ceiling pendant, glass ceiling pendants, glass shade ceiling pendant, glass shade ceiling pendants, glass globe ceiling pendant, glass globe ceiling pendants, glass globe shade ceiling pendant, glass globe shade ceiling pendants, glass LED lighting, glass LED lighting fixture, glass LED lighting fixtures, glass LED shade, glass LED shades, glass shade LED light, glass shade LED lights, glass shade LED lighting, glass LED globe, glass LED globes, glass LED globe shade, glass LED globe shades, glass globe LED light, glass globe LED lighting, glass globe shade LED light, glass globe shade LED lights, glass globe shade LED lighting, glass LED light fixture, glass LED light fixtures, LED glass lighting, LED glass lighting fixture, LED glass lighting fixtures, LED glass shade, LED glass shades, LED glass shade light, LED glass shade lights, LED glass shade lighting, LED glass globe, LED glass globes, LED glass globe shade, LED glass globe shades, LED glass globe light, LED glass globe lighting, LED glass globe shade light, LED glass globe shade lights, LED glass globe shade lighting, LED glass light fixture, LED glass light fixtures, glass LED pendant light, glass LED pendant lights, glass LED pendant lighting, glass LED pendant light fixture, glass LED pendant light fixtures, glass LED pendant lighting fixture, glass LED pendant lighting fixtures, glass shade LED pendant light, glass shade LED pendant lights, glass shade LED pendant lighting, glass globe LED pendant light, glass globe LED pendant lighting, glass globe LED pendant lighting, glass globe LED pendant lights, glass globe shade LED pendant light, glass globe shade LED pendant lights, glass globe shade LED pendant lighting, glass LED ceiling light, glass LED ceiling lights, glass LED ceiling lighting, glass LED ceiling lighting fixture, glass LED ceiling lighting fixtures, glass shade LED ceiling light, glass shade LED ceiling lights, glass shade LED ceiling lighting, glass globe LED ceiling light, glass globe LED ceiling lighting, glass globe shade LED ceiling light, glass globe shade LED ceiling lights, glass globe shade LED ceiling lighting, glass LED ceiling light fixture, glass LED ceiling light fixtures, glass LED ceiling pendant, glass LED ceiling pendants, glass shade LED ceiling pendant, glass shade LED ceiling pendants, glass globe LED ceiling pendant, glass globe LED ceiling pendants, glass globe shade LED ceiling pendant, glass globe shade LED ceiling pendants, LED glass pendant light, LED glass pendant lights, LED glass pendant lighting, LED glass pendant light fixture, LED glass pendant light fixtures, LED glass pendant lighting fixture, LED glass pendant lighting fixtures, LED glass shade pendant light, LED glass shade pendant lights, LED glass shade pendant lighting, LED glass globe pendant light, LED glass globe pendant lighting, LED glass globe pendant lighting, LED glass globe pendant lights, LED glass globe shade pendant light, LED glass globe shade pendant lights, LED glass globe shade pendant lighting, LED glass ceiling light, LED glass ceiling lights, LED glass ceiling lighting, LED glass ceiling lighting fixture, LED glass ceiling lighting fixtures, LED glass shade ceiling light, LED glass shade ceiling lights, LED glass shade ceiling lighting, LED glass globe ceiling light, LED glass globe ceiling lighting, LED glass globe shade ceiling light, LED glass globe shade ceiling lights, LED glass globe shade ceiling lighting, LED glass ceiling light fixture, LED glass ceiling light fixtures, LED glass ceiling pendant, LED glass ceiling pendants, LED glass shade ceiling pendant, LED glass shade ceiling pendants, LED glass globe ceiling pendant, LED glass globe ceiling pendants, LED glass globe shade ceiling pendant, LED glass globe shade ceiling pendants, BLE-S-SCH10, BLE-S-SCH14, SPS-0120, SPS 0120, SPS0120, High lumen series, high lumen collection, high lumen led series, high lumen led collection, High-lumen series, high-lumen collection, high-lumen led series, high-lumen led collection, LED Schoolhouse collection, LED schoolhouse series, the LED schoolhouse collection, the LED schoolhouse series, LED School house collection, LED school house series, the LED school house collection, the LED school house series, primary LED Schoolhouse collection, primary LED schoolhouse series, the primary LED schoolhouse collection, the primary LED schoolhouse series, primary LED School house collection, primary LED school house series, the primary LED school house collection, the primary LED school house series, primary LED collection, primary LED series, the primary LED collection, the primary LED series, high lumen options, striped schoolhouse, striped schoolhouse light,	Primary Schoolhouse High Lumen LED Stem Pendant Light \| Barn Light Electric				765.000000	1547,1595,1756,1804,1847,1851,1925,1926,2002,1929M	246239	Barn Light Electric Company	42	primary-schoolhouse-high-lumen-led-stem-mount-light	1920/1927/1929	B