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

Query time 0.00142

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 = 64899 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
64899	BLE-G-ACN	P	E	C	M	A	A	0	A	1	412.00	1000	0.000	0	0	0	0.00	0	1571840714	0	N	N	N	D	N	P	N	N	N	Y	10	1355979600	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;}	1775761598	Price increased 10% from $281 to $310 - DC 5/24/22 Price increased from $310 to $325 - DC 11/3/22 Price increased from $325 to $412 - DM 9/27/23	Y		none	none	649,651,652,834	BLE-G	{"22791":3,"21941":12,"22927":8,"22928":8,"22798":10,"22799":11,"22800":11,"22801":11}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Acorn Schoolhouse Gooseneck Light			<p>Influenced by vintage designs, this schoolhouse light fits a glass “acorn” shade to an iconic gooseneck arm. With its subtle curves and classic lines, this shade’s timeless charm appeals to both contemporary and traditional décors. Installed for both residential and commercial lighting applications, this wall light ensures every room is properly illuminated and feels uniquely stylish.</p> <p>An abundance of customizing options allows this fixture to take on any space’s look. Hand-painted bands contribute extra hints of character to the design while multiple gooseneck arm shapes offer the right dimensions for every setting. From sophisticated to casual, invigorate any décor with this striking gooseneck light.</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 bands not recommended if opaque glass is selected, as it blends into the glass.</li> </ul>		Bring a classic style to any setting with this schoolhouse gooseneck light! Finish the acorn glass shade with hand-painted bands and create a custom look!	BLE-G-ACN, Acorn Schoolhouse Gooseneck Light, Acorn Schoolhouse Gooseneck, Acorn Gooseneck, Acorn Gooseneck Light, Acorn Wall Light, Acorn Wall Lighting, Acorn Schoolhouse, Schoolhouse acorn, schoolhouse acorn light, schoolhouse acorn gooseneck, schoolhouse gooseneck light, acorn schoolhouse wall light, schoolhouse acorn wall light, Schoolhouse gooseneck, schoolhouse goosenecks, schoolhouse gooseneck light, schoolhouse wall light, schoolhouse, schoolhouse lighting, Acorn School house Gooseneck Light, Acorn School house Gooseneck, Acorn School house, School house acorn, school house acorn light, school house acorn gooseneck, school house gooseneck light, acorn school house wall light, school house acorn wall light, School house gooseneck, school house goosenecks, school house gooseneck light, school house wall light, school house, school house lighting, Acorn, accorn, Glass acorn shade, glass acorn shades, acorn glass shade, acorn glass shades, acorn schoolhouse glass, acorn schoolhouse glass shade, acorn schoolhouse glass shades, schoolhouse acorn glass, schoolhouse acorn glass shade, schoolhouse acorn glass shades, acorn school house glass, acorn school house glass shade, acorn school house glass shades, school house acorn glass, school house acorn glass shade, school house acorn glass shades, Acorn schoolhouse light shade, acorn schoolhouse light shades, acorn schoolhouse lighting shade, acorn schoolhouse lighting shades, Acorn school house light shade, acorn school house light shades, acorn school house lighting shade, acorn school house lighting shades, Glass acorn shade, glass acorn shades, acorn glass shade, acorn glass shades, acorn schoolhouse glass, acorn schoolhouse glass shade, acorn schoolhouse glass shades, schoolhouse acorn glass, schoolhouse acorn glass shade, schoolhouse acorn glass shades, acorn school house glass, acorn school house glass shade, acorn school house glass shades, school house acorn glass, school house acorn glass shade, school house acorn glass shades, Acorn schoolhouse light shade, acorn schoolhouse light shades, acorn schoolhouse lighting shade, acorn schoolhouse lighting shades, Acorn school house light shade, acorn school house light shades, acorn school house lighting shade, acorn school house lighting shades, Glass acorn fixture, glass acorn fixtures, glass acorn light fixture, glass acorn light fixtures, glass acorn lighting fixture, glass acorn lighting fixtures, glass acorn lighting shade, glass acorn lighting shades, glass acorn light shade, glass acorn light shades, acorn glass light shade, acorn glass light shades, acorn glass lighting shade, acorn glass lighting shades, Glass schoolhouse shade, glass schoolhouse shades, schoolhouse glass shade, schoolhouse glass shades, glass schoolhouse light shade, glass schoolhouse light shades, glass schoolhouse lighting shades, glass schoolhouse lighting shade, schoolhouse glass light shade, schoolhouse glass light shades, schoolhouse glass lighting shade, schoolhouse glass lighting shades, Glass school house shade, glass school house shades, school house glass shade, school house glass shades, glass school house light shade, glass school house light shades, glass school house lighting shades, glass school house lighting shade, school house glass light shade, school house glass light shades, school house glass lighting shade, school house glass lighting shades, acorn glass wall light, glass acorn wall light, glass acorn wall lights, glass acorn wall lighting, acorn glass wall light, acorn glass wall lights, acorn glass wall lighting, acorn glass wall fixture, acorn glass wall fixtures, acorn glass wall light fixture, acorn glass wall light fixtures, acorn glass wall lighting fixture, acorn glass wall lighting fixtures, glass acorn wall light, glass acorn wall lights, glass acorn wall lighting, glass acorn wall light fixture, glass acorn wall light fixtures, glass acorn wall lighting fixture, glass acorn wall lighting fixtures, acorn shade wall light, acorn shade wall lights, acorn shade wall lighting, acorn shade wall fixture, acorn shade wall fixtures, acorn shade wall light fixture, acorn shade wall light fixtures, acorn shade wall lighting fixture, acorn shade wall lighting fixtures, acorn glass shade wall light, acorn glass shade wall lights, acorn glass shade wall lighting, acorn glass shade wall fixture, acorn glass shade wall fixtures, acorn glass shade wall light fixture, acorn glass shade wall light fixtures, acorn glass shade wall lighting fixture, acorn glass shade wall lighting fixtures, glass acorn gooseneck light, glass acorn gooseneck lights, glass acorn gooseneck lighting, acorn schoolhouse shade wall light, acorn schoolhouse shade wall lights, acorn schoolhouse shade wall lighting, acorn schoolhouse shade wall fixture, acorn schoolhouse shade wall fixtures, acorn schoolhouse shade wall light fixture, acorn schoolhouse shade wall light fixtures, acorn schoolhouse shade wall lighting fixture, acorn schoolhouse shade wall lighting fixtures, glass shade wall light, glass shade wall lights, glass shade wall lighting, glass shade wall fixture, glass shade wall fixtures, glass shade wall light fixture, glass shade wall light fixtures, glass shade wall lighting fixture, glass shade wall lighting fixtures, Acorn glass gooseneck, glass acorn gooseneck, acorn glass goose neck, glass acorn goose necks, acorn glass gooseneck light, glass acorn gooseneck light, glass acorn gooseneck lights, glass acorn gooseneck lighting, acorn glass gooseneck light, acorn glass gooseneck lights, acorn glass gooseneck lighting, acorn glass goose neck light, glass acorn goose neck light, glass acorn goose neck lights, glass acorn goose neck lighting, acorn glass goose neck light, acorn glass goose neck lights, acorn glass goose neck lighting, Glass shade gooseneck, glass shade goosenecks, glass shade goose neck, glass shade goose necks, glass shade gooseneck light, glass shade gooseneck lights, glass shade gooseneck lighting, glass shade goose neck light, glass shade goose neck lights, glass shade goose neck lighting, Acorn shade gooseneck, acorn shade goose neck, acorn shade goosenecks, acorn shade goose necks, acorn shade gooseneck light, acorn shade gooseneck lights, acorn shade gooseneck lighting, acorn shade goose neck light, acorn shade goose neck lights, acorn shade goose neck lighting, Acorn glass shade gooseneck, acorn glass shade goosenecks, acorn glass shade goose neck, acorn glass shade goose necks, acorn glass shade gooseneck light, acorn glass shade gooseneck lights, acorn glass shade gooseneck lighting, acorn glass shade goose neck light, acorn glass shade goose neck lights, acorn glass shade goose neck lighting, Glass acorn schoolhouse, glass acorn school house, Glass acorn gooseneck, glass acorn goosenecks, glass acorn gooseneck light, glass acorn gooseneck lights, glass acorn gooseneck lighting, glass acorn goose neck light, glass acorn goose neck lights, glass acorn goose neck lighting, Acorn schoolhouse shade gooseneck, acorn schoolhouse shade goosenecks, acorn schoolhouse shade gooseneck light, acorn schoolhouse shade gooseneck lights, acorn schoolhouse shade gooseneck lighting, Acorn schoolhouse shade goose neck, acorn schoolhouse shade goose necks, acorn schoolhouse shade goose neck light, acorn schoolhouse shade goose neck lights, acorn schoolhouse shade goose neck lighting, Glass shade schoolhouse, glass shade school house, glass shade gooseneck, glass shade goosenecks, glass shade gooseneck light, glass shade gooseneck lights, glass shade gooseneck lighting, glass shade goose necks, glass shade goose neck light, glass shade goose neck lights, glass shade goose neck lighting, Goose neck interior, Gooseneck interior, Goose neck light interior, Gooseneck light interior, interior goose neck, interior gooseneck light, interior goose neck light, Clear glass shades, Schoolhouse collection, schoolhouse series, the schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Acorn Schoolhouse collection, Acorn schoolhouse series, the Acorn schoolhouse collection, the schoolhouse series, School house collection, Acorn series, acorn collection, Acorn glass Schoolhouse collection, Acorn glass schoolhouse series, the Acorn glass schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Acorn glass series, acorn glass collection, SPS-0487, SPS0487, SPS 0487, Gooseneck schoolhouse, Gooseneck school house, Goose neck schoolhouse, schoolhouse gooseneck, schoolhouse goosenecks, school house gooseneck, school house goosenecks, school light, school lights, school lighting, school, 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 gooseneck light, Wall mount gooseneck lights, Wall mount gooseneck lighting, Wall mount gooseneck, Wall mount goosenecks, Wall mounted gooseneck light, Wall mounted gooseneck lights, Wall mounted gooseneck lighting, Wall mounted gooseneck, Wall mounted goosenecks, bleg, striped schoolhouse, striped schoolhouse light,	Acorn Schoolhouse Gooseneck Light \| Barn Light Electric				412.000000	1530,1547,1697,1759,1827,1883,2002,1897M	118860	Barn Light Electric Company	10	acorn-schoolhouse-gooseneck-light	1887/1896/1897	B