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

Query time 0.00153

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 = 64353 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": 9,
      "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
64353	BLE-W-DME	P	E	C	M	A	A	0	A	1	375.00	1000	0.000	0	0	0	0.00	0	1513176930	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;}	1747932418	Price increased by $54 - DC 5/6/20 Price increased 5% from $250 to $262 - DC 5/25/21 Price increased 10% from $262 to $289 - DC 5/26/22 Price increased from $289 to $299 - DM 9/28/23 Price increased from $299 to $375 - DM 11/28/23	Y		none	none	580	BLE-W-DME	{"22685":1,"22690":2}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Downtown Swing Arm Wall Sconce			<p>With simplicity at the heart of its design, the Downtown Swing Arm Sconce demonstrates the aesthetic versatility sought in many contemporary spaces. Its straightforward look — consisting solely of a circular backing plate, metal swing arm, and bare bulb socket — conforms to the fashionable minimalist approach while still remaining capable of performing vital tasks within residential and commercial décors. To deliver light precisely where it’s most needed, the swing arm pivots horizontally and is available with an optional swivel knuckle for even more adjustability. However, thanks to the bare bulb socket, several sconces can function as a room’s main source of illumination.</p> <p>For a more creative and modern décor, substitute traditional lighting options for swing arm sconces. Replacing table lamps with these sconces clears up space on night stands and end tables while elevating bedrooms and living spaces with a memorable character. To quickly revamp restaurants and breakfast nooks, consider installing a Downtown Sconce with an extended arm above tables for an innovative new style!</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>Read more about our Downtown Swing Arm Sconce over on our<span> </span><a href="https://www.barnlight.com/inspiration/colorful-swing-arm-wall-sconces-add-whimsical-touch-to-houston-eatery/" target="_blank">blog</a>!</li> </ul>		Adjustable sconce with a fashionable look. Swing Arm Sconces free up space on end tables and night stands. Fully customizable, Made in the USA with Free Shipping!	The Downtown Swing Arm Sconce, The Downtown Swing Arm Sconces, The Downtown Swing Arm Wall Sconce, The Downtown Swing Arm Wall Sconces, Downtown Swing Arm Sconce, Downtown Swing Arm Sconces, Downtown Swing Arm Wall Sconce, Downtown Swing Arm Wall Sconces, The Downtown Minimalist Swing Arm Sconce, The Downtown Minimalist Swing Arm Sconces, The Downtown Minimalist Swing Arm Wall Sconce, The Downtown Minimalist Swing Arm Wall Sconces, Downtown Minimalist Swing Arm Sconce, Downtown Minimalist Swing Arm Sconces, Downtown Minimalist Swing Arm Wall Sconce, Downtown Minimalist Swing Arm Wall Sconces, The Minimalist Swing Arm Sconce, The Minimalist Swing Arm Sconces, The Minimalist Swing Arm Wall Sconce, The Minimalist Swing Arm Wall Sconces, Minimalist Swing Arm Sconce, Minimalist Swing Arm Sconces, Minimalist Swing Arm Wall Sconce, Minimalist Swing Arm Wall Sconces, The Swing Arm Sconce, The Swing Arm Sconces, The Swing Arm Wall Sconce, The Swing Arm Wall Sconces, Swing Arm Sconce, Swing Arm Sconces, Swing Arm Wall Sconce, Swing Arm Wall Sconces, Swing Arm, Swing Arm Light, Swing Arm Lights, Swing Arm Lighting, Swing Arm Wall Light, Swing Arm Wall lights, swing arm wall lighting, Adjustable sconce, adjustable sconces, adjustable wall sconce, adjustable wall sconces, adjustable light, adjustable lights, adjustable lighting, adjustable wall light, adjustable wall lights, adjustable wall lighting, Bare bulb sconce, bare bulb sconces, bare bulb, bare bulb wall sconce, bare bulb wall sconces, bare bulb light, bare bulb lights, bare bulb lighting, bare bulb wall light, bare bulb wall lights, bare bulb wall lighting, bare bulb socket, bare bulb socket, Minimalist swing arm light, minimalist swing arm lights, minimalist swing arm lighting, Minimalist swing arm wall light, minimalist swing arm wall lights, minimalist swing arm wall lighting, The Downtown Swing Arm Light, The Downtown Swing Arm Lights, The Downtown Swing Arm Lighting, The Downtown Swing Arm Wall Light, The Downtown Swing Arm Wall Lights, The Downtown Swing Arm Wall Lighting, downtown, down town, down town minimalist, downtown minimalist, BLE-W-DME-PC, extended arm sconce, extended arm sconces, extended arm wall sconce, extended arm wall sconces, extended arm, downtown swing arm, swing arm, swing arm sconce, swingarm, swingarm, swingarm sconce, swingarm light, swingarm lights, swing arm light, swing arm lights, 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 wall light, wall light bare bulb, bare bulb wall lights, bare bulb wall lighting, bare bulb wall light fixture, bare bulb wall light fixtures, bare bulb wall lighting fixture, bare bulb wall fixture, bare bulb wall fixtures, Bare bulb sconce, bare bulb sconces, bare bulb wall sconce, bare bulb wall sconces, bare bulb sconce light, bare bulb sconce lights, bare bulb sconce lighting, bare bulb sconce light fixture, bare bulb sconce light fixtures, bare bulb sconce lighting fixture, bare bulb sconce lighting fixtures, bare bulb swing arm sconce, swing arm bare bulb sconce, bare bulb swing arm sconces, swing arm bare bulb sconces, bare bulb swing arm wall sconce, swing arm bare bulb wall sconce, bare bulb swing arm wall sconces, swing arm bare bulb wall sconces, bare bulb swing arm sconce light, swing arm bare bulb sconce light, bare bulb swing arm sconce lights, swing arm bare bulb sconce lights, bare bulb swing arm sconce lighting, swing arm bare bulb sconce lighting, bare bulb swing arm sconce light fixture, swing arm bare bulb sconce light fixture, bare bulb swing arm sconce light fixtures, swing arm bare bulb sconce light fixtures, bare bulb swing arm sconce lighting fixture, swing arm bare bulb sconce lighting fixtures, bare bulb adjustable sconce, adjustable bare bulb sconce, bare bulb adjustable sconces, adjustable bare bulb sconces, bare bulb adjustable wall sconce, adjustable bare bulb wall sconce, bare bulb adjustable wall sconces, adjustable bare bulb wall sconces, bare bulb adjustable sconce light, adjustable bare bulb sconce light, bare bulb adjustable sconce lights, adjustable bare bulb sconce lights, bare bulb adjustable sconce lighting, adjustable bare bulb sconce lighting, bare bulb adjustable sconce light fixture, adjustable bare bulb sconce light fixture, bare bulb adjustable sconce light fixtures, adjustable bare bulb sconce light fixtures, bare bulb adjustable sconce lighting fixture, adjustable bare bulb sconce lighting fixtures, swing at plug in, sconce plug in, sconce plug-in, wall sconce plug in, wall sconce plug-in, plug wall sconce, plug sconce, plug wall sconces, plug sconces, wall lamps with plugs, wall lamp with plugs, wall lamps with plug, wall lamp with plug, 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-0055, SPS 0055, SPS0055, swing arm collection, swing arm series, the swing arm collection, the swing arm series, adjustable arms, Interior barn light wall sconce, Interior barn light wall sconces, Interior sconce, interior sconces, interior wall sconce, interior wall sconces, Interior sconce light, Interior sconce lights, Interior sconce lighting, Interior wall light, Interior wall lights, Interior wall lighting, Interior light wall sconce, Interior light wall sconces, Interior light, Interior lights, Interior lighting, wall mount indoor light, wall mount indoor lights, wall mount indoor lighting, wall mount interior light, wall mount interior lights, wall mount interior lighting, wall mounted indoor light, wall mounted indoor lights, wall mounted indoor lighting, wall mounted interior light, wall mounted interior lights, wall mounted interior lighting, interior wall mount light, interior wall mount lights, interior wall mount lighting, interior wall mounted light, interior wall mounted lights, interior wall mounted lighting, wall mount interior sconce, wall mount interior sconces, wall mount interior wall sconce, wall mount interior wall sconces, wall mounted interior sconce, wall mounted interior sconces, wall mounted interior wall sconce, wall mounted interior wall sconces, wall mount indoor sconce, wall mount indoor sconces, wall mount indoor wall sconce, wall mount indoor wall sconces, wall mounted indoor sconce, wall mounted indoor sconces, wall mounted indoor wall sconce, wall mounted indoor wall sconces, Indoor barn light wall sconce, Indoor barn light wall sconces, Indoor sconce, Indoor sconces, Indoor wall sconce, Indoor wall sconces, Indoor sconce light, Indoor sconce lights, Indoor sconce lighting, Indoor wall light, Indoor wall lights, Indoor wall lighting, Indoor light wall sconce, Indoor light wall sconces, Indoor light, Indoor lights, Indoor lighting, Wall mount light, wall mount lights, wall mount lighting, Wall mount light fixture, Wall mount light fixtures, Wall mount lighting fixture, Wall mount lighting fixtures, wall mounted light, wall mounted lights, wall mounted lighting, wall mounted light fixture, wall mounted light fixtures, wall mounted lighting fixture, wall mounted lighting fixtures, Wall mount sconce light, Wall mount sconce lights, Wall mount sconce lighting, Wall mount sconce, Wall mount sconces, Wall mounted sconce light, Wall mounted sconce lights, Wall mounted sconce lighting, Wall mounted sconce, Wall mounted sconces, reading lights, adjustable arm sconce, wall sconce downtown, wall reading lights, swing sconce, plug in barn light, plug-in barn light, plug-in light, swing out light,	Downtown Swing Arm Sconce \| Barn Light Electric				375.000000	1531,1611,1613,1748,1795,1820,1832,1901,1899M	409737	Barn Light Electric Company	116	downtown-swing-arm-sconce	1887/1896/1899	B