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 = 1941 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 65502 
  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 = 65502 
  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 = 65502 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": 7,
      "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
65502	BLE-W-DRM-LED	P	E	C	M	A	A	0	A	1	520.00	1000	0.000	0	0	0	0.00	0	1603733309	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;}	1753110803	Price increased 5% from $558 to $586 - DC 6/1/21 Price increased 10% from $586 to $645 - DC 6/2/22 Price increased from $645 to $520 - DC 11/3/22	Y		none	none	3337,3338,3339	BLE-W	{"22788":1,"22818":2,"22797":3,"22796":3,"22794":3,"22795":3,"22798":4,"22819":5,"22820":6,"21941":7,"22925":1,"22927":3,"22928":3,"22929":1,"22931":3,"22932":3,"22675":5,"22644":6}	1051256	0	0	0	1	0	0	0	0	1	0.00	0	en	Drum LED Schoolhouse Wall Sconce			<p>With its high-performance technology and timeless aesthetic, this schoolhouse wall sconce is a perfect addition to homes and businesses. LED components, fully integrated into the fixture’s design, produce illumination with astonishing efficiency. The result is a functional source of light that significantly reduces power bills and promotes environmental sustainability. <br /><br />The Drum Schoolhouse Sconce features a versatile style, one that equally suits contemporary and traditional décors. Opaque shades cast an atmospheric glow while clear shades provide more brightness. These shades can be finished with hand-painted bands for extra character.</p> <p>\|break\|</p> <ul> <li>Features a customizable glass schoolhouse globe with hand-spun fitter.</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>White painted band(s) not recommended if opaque glass is selected, as it blends into the glass.</span></li> </ul>		In this schoolhouse wall sconce, high-performance LED is integrated into a glass shade. The result is a stylish, unique fixture that’s eco-conscious and environmentally friendly!	BLE-W-DRM-LED, LED Drum Schoolhouse, LED drum school house, drum led schoolhouse, drum led school house, LED drum schoolhouse wall sconce, led drum school house wall sconce, drum led schoolhouse wall sconce, drum led school house wall sconce, drum schoolhouse led wall sconce, drum school house led wall sconce, LED drum schoolhouse wall sconces, led drum school house wall sconces, drum led schoolhouse wall sconces, drum led school house wall sconces, drum schoolhouse led wall sconces, drum school house led wall sconces, LED drum schoolhouse sconce, led drum school house sconce, drum led schoolhouse sconce, drum led school house sconce, drum schoolhouse led sconce, drum school house led sconce, LED drum schoolhouse sconces, led drum school house sconces, drum led schoolhouse sconces, drum led school house sconces, drum schoolhouse led sconces, drum school house led sconces, Drum Schoolhouse, Drum School House, Drum Schoolhouse Wall Sconce, Drum School house Wall sconce, Drum schoolhouse wall sconces, drum school house wall sconces, drum schoolhouse sconce, drum schoolhouse sconces, drum school house sconce, drum school house sconces, drum wall sconce, drum sconce, drum wall sconces, drum sconces, drum schoolhouse wall light, drum school house wall light, drum schoolhouse wall lights, drum school house wall lights, drum schoolhouse wall lighting, drum school house wall lighting, drum schoolhouse sconce light, drum, Glass drum shade, glass drum shades, drum glass shade, drum glass shades, drum schoolhouse glass, drum schoolhouse glass shade, drum schoolhouse glass shades, schoolhouse drum glass, schoolhouse drum glass shade, schoolhouse drum glass shades, drum school house glass, drum school house glass shade, drum school house glass shades, school house drum glass, school house drum glass shade, school house drum glass shades, Drum schoolhouse light shade, drum schoolhouse light shades, drum schoolhouse lighting shade, drum schoolhouse lighting shades, Drum school house light shade, drum school house light shades, drum school house lighting shade, drum school house lighting shades, Glass drum fixture, glass drum fixtures, glass drum light fixture, glass drum light fixtures, glass drum lighting fixture, glass drum lighting fixtures, glass drum lighting shade, glass drum lighting shades, glass drum light shade, glass drum light shades, drum glass light shade, drum glass light shades, drum glass lighting shade, drum 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, drum glass wall light, glass drum wall light, glass drum wall lights, glass drum wall lighting, drum glass wall light, drum glass wall lights, drum glass wall lighting, drum glass wall fixture, drum glass wall fixtures, drum glass wall light fixture, drum glass wall light fixtures, drum glass wall lighting fixture, drum glass wall lighting fixtures, glass drum wall light, glass drum wall lights, glass drum wall lighting, glass drum wall light fixture, glass drum wall light fixtures, glass drum wall lighting fixture, glass drum wall lighting fixtures, drum shade wall light, drum shade wall lights, drum shade wall lighting, drum shade wall fixture, drum shade wall fixtures, drum shade wall light fixture, drum shade wall light fixtures, drum shade wall lighting fixture, drum shade wall lighting fixtures, drum glass shade wall light, drum glass shade wall lights, drum glass shade wall lighting, drum glass shade wall fixture, drum glass shade wall fixtures, drum glass shade wall light fixture, drum glass shade wall light fixtures, drum glass shade wall lighting fixture, drum glass shade wall lighting fixtures, drum schoolhouse shade wall light, drum schoolhouse shade wall lights, drum schoolhouse shade wall lighting, drum schoolhouse shade wall fixture, drum schoolhouse shade wall fixtures, drum schoolhouse shade wall light fixture, drum schoolhouse shade wall light fixtures, drum schoolhouse shade wall lighting fixture, drum 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, Drum glass sconce, drum glass sconces, drum glass wall sconce, drum glass wall sconces, drum glass sconce light, drum glass wall sconce light, glass drum sconce light, glass drum wall sconce light, glass drum wall sconce lights, glass drum sconce lights, glass drum wall sconce lighting, glass drum sconce lighting, drum glass wall sconce light, drum glass sconce light, drum glass wall sconce lights, drum glass sconce lights, drum glass wall sconce lights, drum glass sconce lights, drum glass wall sconce lights, drum glass sconce lights, drum glass wall sconce lighting, drum glass sconce lighting, drum glass wall sconce fixture, drum glass wall sconce fixtures, drum glass wall sconce light fixture, drum glass sconce light fixture, drum glass wall sconce light fixtures, drum glass wall sconce light fixtures, drum glass sconce light fixtures, drum glass wall sconce lighting fixture, drum glass sconce lighting fixture, drum glass wall sconce lighting fixtures, drum glass sconce lighting fixtures, glass drum wall sconce light, glass drum sconce light, glass drum wall sconce lights, glass drum sconce lights, glass drum wall sconce lighting, glass drum sconce lighting, glass drum wall sconce light fixture, glass drum sconce light fixture, glass drum wall sconce light fixtures, glass drum sconce light fixtures, glass drum wall sconce lighting fixture, glass drum sconce lighting fixture, glass drum wall sconce lighting fixtures, glass drum sconce lighting fixtures, Drum shade sconce, drum shade sconces, drum shade wall sconce, drum shade wall sconces, drum shade wall sconce light, drum shade sconce light, drum shade wall sconce lights, drum shade sconce lights, drum shade wall sconce lighting, drum shade sconce lighting, drum shade wall sconce fixture, drum shade sconce fixture, drum shade wall sconce fixtures, drum shade sconce fixtures, drum shade wall sconce light fixture, drum shade sconce light fixture, drum shade wall sconce light fixtures, drum shade sconce light fixtures, drum shade wall sconce lighting fixture, drum shade sconce lighting fixture, drum shade wall sconce lighting fixtures, drum shade sconce lighting fixtures, Drum glass shade sconce, drum glass shade sconces, drum glass shade wall sconce, drum glass shade wall sconces, drum glass shade wall sconce light, drum glass shade sconce light, drum glass shade wall sconce lights, drum glass shade sconce lights, drum glass shade wall lighting, drum glass shade wall sconce fixture, drum glass shade sconce fixture, drum glass shade wall sconce fixtures, drum glass shade sconce fixtures, drum glass shade wall sconce light fixture, drum glass shade sconce light fixture, drum glass shade wall sconce light fixtures, drum glass shade sconce light fixtures, drum glass shade wall sconce lighting fixture, drum glass shade sconce lighting fixture, drum glass shade wall sconce lighting fixtures, drum glass shade sconce lighting fixtures, glass drum sconce, glass drum sconces, glass drum wall sconce, glass drum wall sconces, drum schoolhouse shade wall sconce light, drum schoolhouse shade sconce light, drum schoolhouse shade wall sconce lights, drum schoolhouse shade sconce lights, drum schoolhouse shade wall sconce lighting, drum schoolhouse shade wall sconce lighting, drum schoolhouse shade wall sconce fixture, drum schoolhouse shade wall sconce fixture, drum schoolhouse shade wall sconce fixtures, drum schoolhouse shade sconce fixtures, drum schoolhouse shade wall sconce light fixture, drum schoolhouse shade sconce light fixture, drum schoolhouse shade wall sconce light fixtures, drum schoolhouse shade sconce light fixtures, drum schoolhouse shade wall sconce lighting fixture, drum schoolhouse shade sconce lighting fixture, drum schoolhouse shade wall sconce lighting fixtures, drum schoolhouse shade sconce lighting fixtures, glass shade sconce, glass shade sconces, glass shade wall sconce, glass shade wall sconces, glass shade wall sconce light, glass shade sconce light, glass shade wall sconce lights, glass shade sconce lights, glass shade wall sconce lighting, glass shade sconce lighting, glass shade wall sconce fixture, glass shade sconce fixture, glass shade wall sconce fixtures, glass shade sconce fixtures, glass shade wall sconce light fixture, glass shade sconce light fixture, glass shade wall sconce light fixtures, glass shade sconce light fixtures, glass shade wall sconce lighting fixture, glass shade sconce lighting fixture, glass shade wall sconce lighting fixtures, glass shade sconce lighting fixtures, Schoolhouse collection, schoolhouse series, the schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Drum Schoolhouse collection, Drum schoolhouse series, the Drum schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Drum series, drum collection, SPS-0554, SPS 0554, SPS0554, Schoolhouse sconce collection, schoolhouse sconce series, the schoolhouse sconce collection, the schoolhouse sconce series, School house sconce collection, school house sconce series, the school house sconce collection, the school house sconce series, Drum Schoolhouse sconce collection, Drum schoolhouse sconce series, the Drum schoolhouse sconce collection, the schoolhouse sconce series, School house sconce collection, school house sconce series, the school house sconce collection, the school house sconce series, Drum sconce series, drum sconce collection, Schoolhouse wall sconce collection, schoolhouse wall sconce series, the schoolhouse wall sconce collection, the schoolhouse wall sconce series, School house wall sconce collection, school house wall sconce series, the school house wall sconce collection, the school house wall sconce series, Drum Schoolhouse wall sconce collection, Drum schoolhouse wall sconce series, the Drum schoolhouse wall sconce collection, the schoolhouse wall sconce series, School house wall sconce collection, school house wall sconce series, the school house wall sconce collection, the school house wall sconce series, Drum wall sconce series, drum wall sconce collection, Drum LED schoolhouse Sconce collection, drum LED schoolhouse sconce series, Drum LED school house Sconce collection, drum LED school house sconce series, Drum LED Sconce collection, drum LED sconce series, school light, school lights, school lighting, school, striped schoolhouse, striped schoolhouse light,	Drum LED Schoolhouse Wall Sconce \| Barn Light Electric				520.000000	1547,1592,1847,1863,1925,2002,1941M	100294	Barn Light Electric Company	5	drum-led-schoolhouse-sconce	1920/1938/1941	B