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 = 64908 
  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 = 64908 
  AND (
    companies.status = 'A' 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00135

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 = 64908 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
64908	BLE-FSF-DRM	P	E	C	M	A	A	0	A	1	355.00	1000	0.000	0	0	0	0.00	0	1571852723	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;}	1760030087	Price increased by 5% from $246 to $258 - DC 5/27/21 Price increased by 10% from $258 to $284 - DC 5/24/22 Price increased from $284 to $300 - DC 11/3/22 Price increased from $300 to $355 - DM 9/6/23	Y		none	none	657,658,659,660,3306	BLE-FSF	{"22806":3,"21941":12,"22931":9,"22932":9,"22798":10}	1056013	0	0	0	0	0	0	0	0	1	0.00	0	en	Drum Schoolhouse Semi-Flush Mount Light			<p>Equally decorative and functional, this schoolhouse pendant unites an eye-catching glass shade with a semi-flush mounting. Intended for undersized spaces and areas with low ceilings, the fixture’s mounting lets every room show off an elevated decorative character. Both clear and opaque glass options provide the proper amount of light for any setting.</p> <p>With a wide variety of customizing options, every schoolhouse light can embrace a unique appearance. Choose from several banding patterns, finish colors and materials, and other accessories to create a personalized look for your projects!</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>		An elevated style for any space, our drum schoolhouse semi-flush mount light features a timeless style and is fully customizable!	Drum Schoolhouse Flush Mount Light, Drum Schoolhouse Flush Mount, Drum Flush Mount Light, Schoolhouse Flush Mount Light, Schoolhouse Flush Mount, Drum Schoolhouse, Drum School House, Drum Flush Mount, Drum Flush Mount Light, Drum, Drum Schoolhouse, Drum School House, Drum flush mount pendant, Drum Schoolhouse Flush Mount Pendant, Drum Flush Mount pendant light, Drum School House Flush Mount Light, Drum School House Flush Mount, Drum School House Flush Mount Pendant, 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, Drum Schoolhouse Semi-flush Mount Light, Drum Schoolhouse Semi-flush Mount, Drum Semi-flush Mount Light, Schoolhouse Semi-flush Mount Light, Schoolhouse Semi-flush Mount, Drum Semi-flush Mount, Drum Semi-flush Mount Light, Drum semi-flush mount pendant, Drum Schoolhouse Semi-flush Mount Pendant, Drum Semi-flush Mount pendant light, Drum School House Semi-flush Mount Light, Drum School House Semi-flush Mount, Drum School House Semi-flush Mount Pendant, BLE-FSF-DRM, semi-flush schoolhouse, semi-flush schoolhouse light, semi flush schoolhouse, semi flush schoolhouse light, 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, Glass drum pendant, glass drum pendants, drum glass pendant, drum glass pendants, drum glass ceiling pendant, drum glass ceiling pendants, drum glass pendant light, drum glass pendant lights, drum glass pendant lighting, drum glass ceiling light, drum glass ceiling lights, drum glass ceiling lighting, Glass drum ceiling pendant, glass drum ceiling pendants, glass drum pendant light, glass drum pendant lights, glass drum pendant lighting, glass drum ceiling light, glass drum ceiling lights, glass drum 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, Drum glass schoolhouse flush mount pendant, drum glass schoolhouse flush mount pendants, drum glass schoolhouse flush mount ceiling pendant, drum glass schoolhouse flush mount ceiling pendants, drum glass schoolhouse flush mount pendant light, drum glass schoolhouse flush mount pendant lights, drum glass schoolhouse flush mount pendant lighting, drum glass schoolhouse flush mount ceiling light, drum glass schoolhouse flush mount ceiling lights, drum glass schoolhouse ceiling lighting, glass drum schoolhouse flush mount pendant, glass drum schoolhouse flush mount pendants, glass drum schoolhouse flush mount ceiling pendant, glass drum schoolhouse flush mount ceiling pendants, glass drum schoolhouse flush mount pendant light, glass drum schoolhouse flush mount pendant lights, glass drum schoolhouse flush mount pendant lighting, glass drum schoolhouse flush mount ceiling light, glass drum schoolhouse flush mount ceiling lights, glass drum schoolhouse ceiling lighting, Drum glass school house flush mount pendant, drum glass school house flush mount pendants, drum glass school house flush mount ceiling pendant, drum glass school house flush mount ceiling pendants, drum glass school house flush mount pendant light, drum glass school house flush mount pendant lights, drum glass school house flush mount pendant lighting, drum glass school house flush mount ceiling light, drum glass school house flush mount ceiling lights, drum glass school house ceiling lighting, glass drum school house flush mount pendant, glass drum school house flush mount pendants, glass drum school house flush mount ceiling pendant, glass drum school house flush mount ceiling pendants, glass drum school house flush mount pendant light, glass drum school house flush mount pendant lights, glass drum school house flush mount pendant lighting, glass drum school house flush mount ceiling light, glass drum school house flush mount ceiling lights, glass drum school house ceiling lighting, 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-0486, SPS0486, SPS 0486, Flush mount school house, Flush mount schoolhouse, schoolhouse drum glass, glass schoolhouse drum, glass schoolhouse drum shade, glass schoolhouse drum shades, glass schoolhouse drum shade, glass schoolhouse drum shades, schoolhouse drum flush mount, drum schoolhouse flush mount, schoolhouse drum flush mount pendant, drum schoolhouse flush mount pendants, schoolhouse drum flush mount light, drum schoolhouse flush mount lights, schoolhouse drum flush mount lighting, school house drum glass, glass school house drum, glass school house drum shade, glass school house drum shades, glass school house drum shade, glass school house drum shades, school house drum flush mount, drum school house flush mount, school house drum flush mount pendant, drum school house flush mount pendants, school house drum flush mount light, drum school house flush mount lights, school house drum flush mount lighting, drum schoolhouse semi flush, drum schoolhouse semi-flush, Small mount lighting, Small mount light, Small mount lights, foyer light, foyer lights, foyer lighting, school light, school lights, school lighting, school, ceiling flush mount, milk glass ceiling mount, striped schoolhouse, striped schoolhouse light, drum semi flush, semi flush drum, semi flush drum light, drum light semi flush,	Drum Schoolhouse Semi-Flush Mount Light \| Barn Light Electric				355.000000	1547,1607,1703,1757,1827,1883,1892,2002M	287037	Barn Light Electric Company	25	drum-schoolhouse-semi-flush-mount-light	2002	B