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

Query time 0.00146

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": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "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": 1,
      "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')"
    },
    "block-nl-join": {
      "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": "cscart_product_prices.product_id = 64349 and cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1)"
      },
      "buffer_type": "flat",
      "buffer_size": "8Kb",
      "join_type": "BNL",
      "attached_condition": "trigcond(1) and trigcond(cscart_product_prices.product_id = 64349 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_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
64349	BLE-S-CH029	P	E	C	M	D	A	0	A	1	600.00	1000	0.000	0	0	0	0.00	0	1511966999	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;}	1779997847	Price reduced from $625 to $519 per SG - DC 11/11/19 Price increased 5% from $519 to $545 - DC 5/28/21 Price increased 10% from $545 to $600 - DC 5/24/22	Y		none	none	610	BLE-S-CH029	{"22683":1,"22684":2,"21970":1,"23121":2}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Downtown Minimalist 5-Light Chandelier			<p>Simplicity is a basic hallmark of contemporary trends, but that doesn’t mean these designs need to lack imagination. To fully embrace minimalism, the Downtown Chandelier attaches bare bulb sockets to narrow metal arms, which are creatively arranged and assembled to establish an eye-catching structure. With this clean, subtle appearance, the Downtown is crucial to cultivating an elevated modern décor.</p> <p>As a memorable focal point in kitchens, entryways, and living spaces, this chandelier attracts attention to define the room’s look. However, with its streamlined profile, the Downtown can additionally put the finishing touches on nearly completed styles. These decorative benefits extend to commercial buildings, where the chandelier helps restaurants or retail stores feel uniquely fashionable!</p> <p>\|break\|</p> <ul> <li>Manufactured in the U.S.A.</li> <li><span>This light fixture is made-to-order to suit your custom specifications. Learn more about the process </span><a href="https://www.barnlight.com/behind-the-scenes-video-footage/" target="_blank">here</a><span>!</span></li> <li><span>Read more about our Downtown Minimalist 5-Light Chandelier over on our <a href="https://www.barnlight.com/inspiration/bright-energetic-colors-add-splash-of-midcentury-fun-to-hotel-remodel/" target="_blank">blog</a>!</span></li> </ul>		Embracing minimalism, this chandelier shows off the latest styles! Regularly included in residential and commercial settings! Customizable and Free Shipping!	BLE-S-CH029-PC, The Downtown Minimalist 5-Light Chandelier, The Downtown Minimalist 5-Light Chandeliers, Downtown Minimalist 5-Light Chandelier, The Downtown Minimalist 5-Light Chandeliers, Downtown Minimalist 5-Light Chandeliers, The Downtown Minimalist 5 Light Chandelier, The Downtown Minimalist 5 Light Chandeliers, Downtown Minimalist 5 Light Chandelier, The Downtown Minimalist 5 Light Chandeliers, Downtown Minimalist 5 Light Chandeliers, The Minimalist 5-Light Chandelier, The Minimalist 5-Light Chandeliers, Minimalist 5-Light Chandelier, The Minimalist 5-Light Chandeliers, Minimalist 5-Light Chandeliers, The Minimalist 5 Light Chandelier, The Minimalist 5 Light Chandeliers, Minimalist 5 Light Chandelier, The Minimalist 5 Light Chandeliers, Minimalist 5 Light Chandeliers, The Downtown 5-Light Chandelier, The Downtown 5-Light Chandeliers, Downtown 5-Light Chandelier, The Downtown 5-Light Chandeliers, Downtown 5-Light Chandeliers, The Downtown 5 Light Chandelier, The Downtown 5 Light Chandeliers, Downtown 5 Light Chandelier, The Downtown 5 Light Chandeliers, Downtown 5 Light Chandeliers, The Downtown Minimalist Chandelier, Downtown Minimalist Chandelier, The Downtown Minimalist Chandeliers, Downtown Minimalist Chandeliers, The Downtown Chandelier, Downtown Chandelier, The Downtown Chandeliers, Downtown Chandeliers, The Minimalist Chandelier, Minimalist Chandelier, The Minimalist Chandeliers, Minimalist Chandeliers, Modern chandelier, modern chandeliers, contemporary chandelier, contemporary chandeliers, The Downtown Minimalist Pendant, The Downtown Minimalist Pendants, The Downtown Minimalist Pendant light, The Downtown Minimalist Pendant lights, The Downtown Minimalist Pendant lighting, The Downtown Minimalist Ceiling Pendant, The Downtown Minimalist Ceiling Pendants, The Downtown Minimalist Ceiling Light, The Downtown Minimalist Ceiling Lights, The Downtown Minimalist Ceiling Lighting, The Minimalist Pendant, The Minimalist Pendants, The Minimalist Pendant light, The Minimalist Pendant lights, The Minimalist Pendant lighting, The Minimalist Ceiling Pendant, The Minimalist Ceiling Pendants, The Minimalist Ceiling Light, The Minimalist Ceiling Lights, The Minimalist Ceiling Lighting, The Downtown Pendant, The Downtown Pendants, The Downtown Pendant light, The Downtown Pendant lights, The Downtown Pendant lighting, The Downtown Ceiling Pendant, The Downtown Ceiling Pendants, The Downtown Ceiling Light, The Downtown Ceiling Lights, The Downtown Ceiling Lighting, midcentury, mid-century, mid century, mid century chandelier, midcentury chandelier, mid-century chandelier, midcentury modern, mid century modern, mid-century modern, new downtown and bubble, bubble light, downtown, down town, downtown minimalist, down town minimalist, Bare bulb light, bare bulb lights, bare bulb lighting, bare bulb light fixture, bare bulb light fixtures, bare bulb lighting fixture, bare bulb lighting fixtures, Bare bulb pendant, bare bulb pendants, bare bulb ceiling pendant, bare bulb ceiling pendants, bare bulb pendant light, bare bulb pendant lights, bare bulb pendant lighting, bare bulb ceiling light, bare bulb ceiling lights, bare bulb ceiling lighting, Bare bulb chandelier, bare bulb chandeliers, bare bulb chandelier fixture, bare bulb chandelier fixtures, bare bulb multi light, bare bulb multilight, bare bulb multilights, bare bulb multilighting, bare bulb multi light, bare bulb multi lights, bare bulb multi lighting, bare bulb multi-light, bare bulb multi-lights, bare bulb multi-lighting, bare bulb multiple light, bare bulb multiple lights, bare bulb multiple lighting, multiple lighting fixtures, multiple light fixture, multiple light fixtures, multiple lighting fixture, chandelier with multiple fixtures, chandelier with multiple fixture, chandeliers with multiple fixtures, chandeliers with multiple light fixture, chandelier with multiple light fixtures, chandelier with multiple lighting fixture, chandelier with multiple lighting fixtures, chandeliers with multiple light fixtures, chandeliers with multiple lighting fixtures, downlight minimalist, down light minimalist, down minimalist, Downtown Collection, Downtown Series, The Downtown Collection, The Downtown Series, Down town Collection, Down town Series, The Down town Collection, The Down town Series, Minimalist collection, Minimalist series, The Minimalist collection, The Minimalist series, Downtown Minimalist Collection, Downtown Minimalist Series, The Downtown Minimalist Collection, The Downtown Minimalist Series, Down town Minimalist Collection, Down town Minimalist Series, The Down town Minimalist Collection, The Down town Minimalist Series, SPS-0422, SPS 0422, SPS0422, foyer light, foyer lights, foyer lighting, foyer light, foyer lights, foyer lighting,	Downtown Minimalist 5-Light Chandelier \| Barn Light Electric				600.000000	2010M	1432140	Barn Light Electric Company		downtown-minimalist-5-light-chandelier	1887/1888/2010	B