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

Query time 0.00151

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 = 64876 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
64876	BLE-F-RND	P	E	C	M	A	A	0	A	1	332.00	1000	0.000	0	0	0	0.00	0	1571763294	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;}	1737047827	Price increased by 5% from $180 to $189 - DC 5/27/21 Price increased by 10% from $189 to $208 - DC 5/24/22 Price increased from $208 to $225 - DC 11/3/22 Price increased from $225 to $240 - DC 11/3/22 Price increased from $240 to $332 - DM 9/26/23	Y		none	none	653,654,655,656,3310	BLE-F	{"21941":12,"22923":9,"22924":9,"22798":10}	1056013	0	0	0	0	0	0	0	0	1	0.00	0	en	Round Schoolhouse Flush Mount Light			<p>Easily incorporated into any décor, this pendant’s 10" round glass schoolhouse shade and flush mounting provide a versatile sense of style and timeless appearance. At home in both contemporary and traditional settings, this fixture can function as either a focal point or an accent piece. Hand-painted bands give the globe-shaped shade a sophisticated, upscale character.</p> <p>Other customizing options, like finish colors and glass styles, further dictate the fixture’s aesthetic. With endless combinations and possibilities, every space can appear unique and elevated.</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 bands not recommended if Opaque Glass is selected, as it blends into the glass.</span></li> </ul>		Timeless and eye-catching, this schoolhouse flush mount light gives undersized spaces their own sense of style! Customizable with hand-painted bands!	Schoolhouse, school house, schoolhouse flush, school house flush, schoolhouse pendant, schoolhouse pendant light, schoolhouse ceiling pendant, schoolhouse ceiling pendants, school house pendant, school house pendant light, school house ceiling pendant, school house ceiling pendants, BLE-F-RND, Round Schoolhouse Flush Mount Light, Round School house Flush Mount Light, Round Schoolhouse Flush Mount, Round School house Flush Mount, Round Schoolhouse Flush Mount, Round Schoolhouse Flush Mount Light, Round Flush Mount, Round Flush Mount Light, Schoolhouse globe, Schoolhouse globe flush mount, globe flush mount light, globe flush mount, glass globe, Glass round shade, glass round shades, round glass shade, round glass shades, round schoolhouse glass, round schoolhouse glass shade, round schoolhouse glass shades, schoolhouse round glass, schoolhouse round glass shade, schoolhouse round glass shades, round school house glass, round school house glass shade, round school house glass shades, school house round glass, school house round glass shade, school house round glass shades, Round schoolhouse light shade, round schoolhouse light shades, round schoolhouse lighting shade, round schoolhouse lighting shades, Round school house light shade, round school house light shades, round school house lighting shade, round school house lighting shades, Glass round fixture, glass round fixtures, glass round light fixture, glass round light fixtures, glass round lighting fixture, glass round lighting fixtures, glass round lighting shade, glass round lighting shades, glass round light shade, glass round light shades, round glass light shade, round glass light shades, round glass lighting shade, round 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, Glass globe shade, glass globe shades, glass globe shade light, glass globe shade lights, glass globe shade lighting, glass schoolhouse globe, glass school house globe, glass schoolhouse globe, glass school house globes, glass globe schoolhouse light, glass globe schoolhouse lights, glass globe schoolhouse lighting, glass globe school house light, glass globe school house lights, glass globe school house lighting, glass globe fixture, glass globe fixtures, glass globe light fixture, glass globe light fixtures, glass globe lighting fixture, glass globe lighting fixtures, glass globe shade light, glass globe shade lights, glass globe shade lighting, glass globe shade light fixture, glass globe shade light fixtures, glass globe shade lighting fixture, glass globe shade lighting fixtures, glass globe schoolhouse light fixture, glass globe school house light fixture, glass globe schoolhouse light fixtures, glass globe school house light fixtures, glass globe schoolhouse lighting fixture, glass globe school house lighting fixture, glass globe schoolhouse lighting fixtures, glass globe school house lighting fixtures, Glass schoolhouse shade, glass schoolhouse shades, glass school house shade, glass school house shades, glass schoolhouse light, glass schoolhouse lights, glass schoolhouse lighting, Glass round pendant, glass round pendants, round glass pendant, round glass pendants, round glass ceiling pendant, round glass ceiling pendants, round glass pendant light, round glass pendant lights, round glass pendant lighting, round glass ceiling light, round glass ceiling lights, round glass ceiling lighting, Glass round ceiling pendant, glass round ceiling pendants, glass round pendant light, glass round pendant lights, glass round pendant lighting, glass round ceiling light, glass round ceiling lights, glass round ceiling lighting, Glass schoolhouse pendant, glass schoolhouse pendants, glass schoolhouse ceiling pendant, glass schoolhouse ceiling pendants, glass schoolhouse pendant light, glass schoolhouse pendant lights, glass schoolhouse pendant lighting, glass schoolhouse ceiling light, glass schoolhouse ceiling lights, glass schoolhouse ceiling lighting, Schoolhouse glass pendant, schoolhouse glass pendants, schoolhouse glass ceiling pendant, schoolhouse glass ceiling pendants, schoolhouse glass pendant light, schoolhouse glass pendant lights, schoolhouse glass pendant lighting, schoolhouse glass ceiling light, schoolhouse glass ceiling lights, schoolhouse glass ceiling lighting, Glass school house pendant, glass school house pendants, glass school house ceiling pendant, glass school house ceiling pendants, glass school house pendant light, glass school house pendant lights, glass school house pendant lighting, glass school house ceiling light, glass school house ceiling lights, glass school house ceiling lighting, School house glass pendant, school house glass pendants, school house glass ceiling pendant, school house glass ceiling pendants, school house glass pendant light, school house glass pendant lights, school house glass pendant lighting, school house glass ceiling light, school house glass ceiling lights, school house glass ceiling lighting, Glass globe pendant, glass globe pendants, glass globe pendant light, glass globe pendant lights, glass globe pendant lighting, glass globe ceiling pendant, glass globe ceiling pendants, glass globe ceiling light, glass globe ceiling lights, glass globe ceiling lighting, Glass globe schoolhouse pendant, glass globe school house pendant, glass globe schoolhouse pendants, glass globe school house pendants, glass globe schoolhouse pendant light, glass globe school house pendant light, glass globe schoolhouse pendant lights, glass globe school house pendant lights, glass globe schoolhouse pendant lighting, glass globe school house pendant lighting, glass globe schoolhouse ceiling pendant, glass globe schoolhouse pendants, glass globe school house ceiling pendant, glass globe school house ceiling pendants, Globe schoolhouse pendant, globe schoolhouse pendants, globe schoolhouse ceiling pendant, globe schoolhouse ceiling pendants, globe school house ceiling pendant, globe school house ceiling pendants, globe schoolhouse pendant light, globe school house pendant lights, globe school house pendant lighting, globe schoolhouse ceiling light, globe schoolhouse ceiling lights, globe schoolhouse ceiling lighting, globe school house ceiling light, globe school house ceiling lights, globe school house ceiling lighting, Schoolhouse globe pendant, schoolhouse globe pendants, schoolhouse globe ceiling pendant, schoolhouse globe ceiling pendants, schoolhouse globe pendant light, schoolhouse globe pendant lights, schoolhouse globe pendant lighting, schoolhouse globe ceiling light, schoolhouse globe ceiling lights, schoolhouse globe ceiling lighting, School house globe pendant, school house globe pendants, school house globe ceiling pendant, school house globe ceiling pendants, school house globe pendant light, school house globe pendant lights, school house globe pendant lighting, school house globe ceiling light, school house globe ceiling lights, school house globe ceiling lighting, Round glass schoolhouse flush mount pendant, round glass schoolhouse flush mount pendants, round glass schoolhouse flush mount ceiling pendant, round glass schoolhouse flush mount ceiling pendants, round glass schoolhouse flush mount pendant light, round glass schoolhouse flush mount pendant lights, round glass schoolhouse flush mount pendant lighting, round glass schoolhouse flush mount ceiling light, round glass schoolhouse flush mount ceiling lights, round glass schoolhouse ceiling lighting, glass round schoolhouse flush mount pendant, glass round schoolhouse flush mount pendants, glass round schoolhouse flush mount ceiling pendant, glass round schoolhouse flush mount ceiling pendants, glass round schoolhouse flush mount pendant light, glass round schoolhouse flush mount pendant lights, glass round schoolhouse flush mount pendant lighting, glass round schoolhouse flush mount ceiling light, glass round schoolhouse flush mount ceiling lights, glass round schoolhouse ceiling lighting, Round glass school house flush mount pendant, round glass school house flush mount pendants, round glass school house flush mount ceiling pendant, round glass school house flush mount ceiling pendants, round glass school house flush mount pendant light, round glass school house flush mount pendant lights, round glass school house flush mount pendant lighting, round glass school house flush mount ceiling light, round glass school house flush mount ceiling lights, round glass school house ceiling lighting, glass round school house flush mount pendant, glass round school house flush mount pendants, glass round school house flush mount ceiling pendant, glass round school house flush mount ceiling pendants, glass round school house flush mount pendant light, glass round school house flush mount pendant lights, glass round school house flush mount pendant lighting, glass round school house flush mount ceiling light, glass round school house flush mount ceiling lights, glass round school house flush mount ceiling lighting, Globe flush mount, globe flush mounts, globe shade flush mount, globe shade flush mounts, globe shade flush mount ceiling pendant, globe shade flush mount ceiling pendants, globe shade flush mount pendant light, globe shade flush mount pendant lights, globe shade flush mount pendant lighting, globe shade flush mount ceiling light, globe shade flush mount ceiling lights, globe shade flush mount ceiling lighting, globe flush mount light, globe flush mount lights, globe flush mount lighting, Glass globe flush mount, glass globe flush mounts, glass globe flush mount light, glass globe flush mount lights, glass globe flush mount lighting, glass globe flush mount ceiling pendant, glass globe flush mount ceiling pendants, glass globe ceiling light, glass globe ceiling lights, glass globe ceiling lighting, round house, round school house, Schoolhouse collection, schoolhouse series, the schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Round Schoolhouse collection, Round schoolhouse series, the Round schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Round series, round collection, Round glass Schoolhouse collection, Round glass schoolhouse series, the Round glass schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Round glass series, round glass collection, SPS-0488, SPS 0488, SPS0488, Flush mount school house, Flush mount schoolhouse, Small mount lighting, Small mount light, Small mount lights, Flushmount schoolhouse lights, foyer light, foyer lights, foyer lighting, school light, school lights, school lighting, school, round glass series, ceiling flush mount, striped schoolhouse, striped schoolhouse light,	Round Schoolhouse Flush Mount Light \| Barn Light Electric				332.000000	1547,1607,1703,1758,1892,2002M	143541	Barn Light Electric Company	10	round-schoolhouse-flush-mount-light	2002	B