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

Query time 0.00172

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 = 63758 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
63758	BLE-G-CGG-V2-LED	P	E	C	M	W	D	0	A	1	541.00	1000	0.000	0	0	0	0.00	0	1461556800	0	N	N	N	D	N	R	N	N	N	Y	10	1461556800	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;}	1776869577	Price increased 5% from $468 to $491 - DC 6/1/21 Price increased 10% from $491 to $541 - DC 6/2/22	Y		none	none	1013,1014	BLE-G-CGG-V2	{"23015":2,"23016":3,"23023":4,"23021":4,"23022":4,"23116":5,"22644":6,"24039":3,"24041":3}	1008383	0	0	0	1	0	0	0	0	1	0.00	0	en	Atomic Topless Dual LED Wall Sconce			<p>Whether it’s used to heighten existing decors or as a statement piece in brand new designs, the Atomic Topless Dual LED Sconce is an ideal addition to any location! Based on industrial fixtures, this wall sconce finds a creative new take on vintage looks. Matching the needs of contemporary designers and modern spaces, the “v-shaped” structure enlivens both residential and commercial venues. Intended for both indoor and outdoor installation, the Dual Sconce provides its unique charms to any setting.</p> <p>By incorporating cutting-edge LED components into the two rustic guards, this wall sconce can produce illumination with unmatched efficiency. Ecologically and financially friendly, installing LED wall sconces is a simple and effective way to reduce your home or business’s overall energy demands.</p> <p>\|break\|</p> <ul> <li><span>This product features two d</span><span>ie cast aluminum<span> </span></span><span>guards and customizable glass</span><span>.</span></li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>.</li> <li>Read more about the Atomic Topless Dual LED Sconce over on our<span> <a href="https://www.barnlight.com/inspiration/dual-wall-sconce-offers-twice-the-light-twice-the-style/" target="_blank">blog</a></span>! </li> </ul>		Fitted with technologically advanced LED components, the Atomic Topless Dual Sconce shows off efficient technology and a fully customizable aesthetic!	BLE-G-CGG/V2-LED, Atomic Topless Dual Sconce, Atomic Topless Dual Sconces, Atomic Topless Dual Sconce light, Atomic Topless Dual Sconce lights, Atomic Topless Dual Sconce lighting, Atomic Dual Sconce, Atomic Dual Sconces, Atomic Dual Sconce light, Atomic Dual Sconce lights, atomic dual sconce lighting, Atomic Topless Dual Wall Sconce, Atomic Topless Dual Wall Sconces, Atomic Topless Dual wall Sconce light, Atomic Topless Dual wall Sconce lights, Atomic Topless Dual wall Sconce lighting, Atomic Dual wall Sconce, Atomic Dual wall Sconces, Atomic Dual wall Sconce light, Atomic Dual wall Sconce lights, atomic dual wall sconce lighting, Topless Dual Sconce, Topless Dual Sconces, Topless Dual Sconce light, Topless Dual Sconce lights, Topless Dual Sconce lighting, Dual Sconce, Dual Sconces, Dual Sconce light, Dual Sconce lights, dual sconce lighting, Topless Dual Wall Sconce, Topless Dual Wall Sconces, Topless Dual wall Sconce light, Topless Dual wall Sconce lights, Topless Dual wall Sconce lighting, Dual wall Sconce, Dual wall Sconces, Dual wall Sconce light, Dual wall Sconce lights, dual wall sconce lighting, Atomic Topless Sconce, Atomic Topless Sconces, Atomic Topless Sconce light, Atomic Topless Sconce lights, Atomic Topless Sconce lighting, Atomic Sconce, Atomic Sconces, Atomic Sconce light, Atomic Sconce lights, atomic sconce lighting, Atomic Topless Wall Sconce, Atomic Topless Wall Sconces, Atomic Topless wall Sconce light, Atomic Topless wall Sconce lights, Atomic Topless wall Sconce lighting, Atomic wall Sconce, Atomic wall Sconces, Atomic wall Sconce light, Atomic wall Sconce lights, atomic wall sconce lighting, Topless Sconce, Topless Sconces, Topless Sconce light, Topless Sconce lights, Topless Sconce lighting, Sconce, Sconces, Sconce light, Sconce lights, sconce lighting, Topless Wall Sconce, Topless Wall Sconces, Topless wall Sconce light, Topless wall Sconce lights, Topless wall Sconce lighting, wall Sconce, wall Sconces, wall Sconce light, wall Sconce lights, wall sconce lighting, Atomic Topless Dual wall light, Atomic Topless Dual wall lights, Atomic Topless Dual wall lighting, Atomic Dual wall light, Atomic Dual wall lights, Atomic Dual wall lighting, Dual wall light, Dual wall lights, Dual wall lighting, Dual wall light, Dual wall lights, Dual wall lighting, Atomic duel, atomic duel wall light, atomic duel wall lights, atomic duel wall lighting, atomic duel sconce, atomic duel sconces, atomic duel sconce light, atomic duel sconce lights, atomic duel sconce lighting, atomic, topless, dual, duel, Atomic Topless Dual LED Sconce, Atomic Topless Dual LED Sconces, Atomic Topless Dual LED wall Sconce, Atomic Topless Dual LED wall Sconces, Atomic Dual LED Sconce, Atomic Dual LED Sconces, Atomic Dual LED wall Sconce, Atomic Dual LED wall Sconces, Atomic Topless Dual LED Sconce light, Atomic Topless Dual LED Sconce lights, Atomic Topless Dual LED Sconce lighting, Atomic Dual LED Sconce light, Atomic Dual LED Sconce lights, Atomic Dual LED Sconce lighting, Dual led sconce light, dual led sconce lights, dual led sconce lighting, dual led wall sconce, dual led wall sconce, led wall sconce, led wall sconces, led wall light, led wall lights, led wall lighting, led dual sconce, led dual sconces, led dual sconce light, led dual sconce lights, led dual sconce lighting, led dual wall sconce, led dual wall sconces, led dual wall light, led dual wall lights, led dual wall lighting, Rugged guard light, rugged guard lights, rugged guard lighting, rugged guard light fixture, rugged guard lighting fixture, rugged guard light fixtures, rugged guard lighting fixtures, Rugged guard sconce, rugged guard wall sconce, rugged guard sconce light, rugged guard sconce lights, rugged guard sconce lighting, rugged guard wall light, rugged guard wall lights, rugged guard wall lighting, Rugged guard LED light, rugged guard LED lights, rugged guard LED lighting, rugged guard LED light fixture, rugged guard LED lighting fixture, rugged guard LED light fixtures, rugged guard LED lighting fixtures, Rugged guard LED sconce, rugged guard LED wall sconce, rugged guard LED sconce light, rugged guard LED sconce lights, rugged guard LED sconce lighting, rugged guard LED wall light, rugged guard LED wall lights, rugged guard LED wall lighting, SPS-0033, SPS 0033, SPS0033, cast guard collection, cast guard series, rugged guard collection, rugged guard series, industrial guard collection, industrial guard series, cgg collection, cgg series, CGG LED Sconce, CGG LED Sconces, CGG LED Wall Sconce, CGG LED Wall Sconces, CGG LED Sconce Light, CGG LED Sconce Lights, CGG LED Sconce Lighting, Bathroom vanity light fixture, Bathroom vanity light fixtures, Bathroom vanity fixture, Bathroom vanity fixtures, Bathroom vanity light, Bathroom vanity lights, Bathroom vanity lighting, Bath vanity light, Bath vanity lights, Bath vanity lighting, Bath vanity fixture, Bath vanity fixtures, bathroom light fixtures, bathroom light fixture, bathroom lighting fixtures, bathroom lighting fixture, bath light fixtures, bath light fixture, bath lighting fixtures, bath lighting fixture, ble-c-gg, nautical cage sconce, double atomic, dual arm sconces,	Atomic Topless Dual LED Wall Sconce \| Barn Light Electric				541.000000	1592,1857,1863,1935,2001,1941M	222635	Barn Light Electric Company	15	atomic-topless-dual-led-sconce	1920/1938/1941	B