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

Query time 0.00127

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 = 64719 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": 7,
      "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
64719	BLE-G-RWS	P	E	C	M	W	A	0	A	1	275.00	1000	0.000	0	0	0	0.00	0	1562184510	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;}	1769543765	Price increased by $69 - DC 4/21/21 37% commercial discount to base price manually added - DC 4/21/21 Price increased 5% from $238 to $250 - DC 5/24/21 Price increased 10% from $250 to $275 - DC 5/24/22	Y		none	none	165,166,167	BLE-G		1008383	0	0	0	0	0	0	0	0	1	0.00	0	en	Seaside Gooseneck Light			<p>Influenced by 1930s-era gas station lighting, the Seaside’s shallow, fluted shade is designed to stand out. By embracing an inherently playful shape, the Seaside complements eclectic modern décors, trendy urban businesses, and other similar spaces. Mounted to a sturdy gooseneck arm, the Seaside commands attention, establishing an enviable curb appeal.</p> <p>The Seaside’s shade is constructed entirely by hand in the United States, reinforcing its eye-catching aesthetics with meticulous craftsmanship. Different customization and finish options, like our weather-resistant marine grade, help the Seaside embrace its own unique personality! </p> <p>\|break\|</p> <ul> <li><span data-contrast="auto" xml:lang="EN-US" lang="EN-US" class="TextRun SCXW69328347 BCX0"><span class="NormalTextRun SCXW69328347 BCX0">Hand-built</span><span class="NormalTextRun SCXW69328347 BCX0"><span> </span>with careful attention to detail, this industrial fixture is a dependable indoor/outdoor lighting option</span><span class="NormalTextRun SCXW69328347 BCX0">.</span></span><span class="EOP SCXW69328347 BCX0" data-ccp-props="{"201341983":0,"335559739":160,"335559740":259}"> </span></li> <li>Made-to-order, learn more about the process<span> </span><a href="/video-gallery/">here</a>.</li> <li>Learn more about the Seaside Gooseneck Light over on our<span> <a href="/inspiration/all-you-need-to-know-fun-casual-radial-wave-shades/" target="_blank">blog</a>!</span></li> </ul>	radial RLM shade, radial wave light, gas station reflector, gas station gooseneck light, barn wall light, retro gooseneck lighting, decorative restaurant exterior light, festive patio light, fluted shade, gas station light fixture	Bring handcrafted, American made gooseneck lighting to any space with the Seaside! This indoor/outdoor fixture features a playful retro look with modern appeal!	The Starfire Radial Wave Reflector Gooseneck Light, BLE-G-RWS-PC, radial wave gooseneck, starfire gooseneck, reflector gooseneck, radial wave, H-19008-RW, BLE-G-RWS12-PC, BLE-G-RWS14-PC, BLE-G-RWS16-PC, Starfire gooseneck light, Starfire gooseneck lighting, Starfire gooseneck wall light, radial wave gooseneck, radial wave gooseneck light, radial wave gooseneck lighting, radial wave gooseneck wall light, radial wave reflector gooseneck, radial wave reflector wall light, radial wave reflector gooseneck lighting, radial wave reflector gooseneck light, the radial wave reflector, the radial wave gooseneck, the radial wave wall light, the radial wave gooseneck lighting, the Starfire gooseneck light, the Starfire gooseneck lighting, new rlm, new listing, new seaside, Goodrich Seaside, Goodrich radial wave, Goodrich radial wave reflector, Seaside gooseneck, radial wave gooseneck, radial wave reflector gooseneck, BLE-G-RWS, BLE-G-RWS12, BLE-G-RWS14, BLE-G-RWS16, seaside, radial gooseneck, seaside gooseneck, seaside radial wave, star fire, starfire, Light with photocell, outdoor light light with photocell, Goose neck with fluted shade, Gooseneck with fluted shade, Goose neck with fluted shades, Gooseneck with fluted shades, seaside series, seaside collection, sea side series, sea side collection, the seaside series, the seaside collection, the sea side series, the sea side collection, SPS-0156, SPS0156, SPS 0156, outdoor gooseneck barn lights, outdoor gooseneck barn light, outdoor gooseneck barn lighting, Warehouse Gooseneck Lights, Warehouse Gooseneck Light, Warehouse Gooseneck Lighting, Ware house Gooseneck Lights, Ware house Gooseneck Light, Ware house Gooseneck Lighting, Warehouse Goose neck Lights, Warehouse Goose neck Light, Warehouse Goose neck Lighting, Ware house Goose neck Lights, Ware house Goose neck Light, Ware house Goose neck Lighting, Warehouse Gooseneck, Warehouse Goosenecks, Ware house Gooseneck, Ware house Goosenecks, Ware house Goose neck, Ware house Goose necks, Gooseneck Warehouse Lights, Gooseneck Warehouse Light, Gooseneck Warehouse Lighting, Gooseneck Ware house Lights, Gooseneck Ware house Light, Gooseneck Ware house Lighting, Goose neck Warehouse Lights, Goose neck Warehouse Light, Goose neck Warehouse Lighting, Goose neck Ware house Lights, Goose neck Ware house Light, Goose neck Ware house Lighting, Gooseneck Warehouse, Gooseneck Ware house, Goose neck Warehouse, Goose neck Ware house, Seaside nautical barn light, Seaside nautical barn lights, Seaside nautical barn lighting, Sea side nautical barn light, Sea side nautical barn lights, Sea side nautical barn lighting, Outside lights, Outside light, Outside lighting, Out side lights, Out side light, Out side lighting, barn light for outside, barn lights for outside, Outside gooseneck barn light, Outside gooseneck barn lights, Outside gooseneck barn lighting, Outside gooseneck light, Outside gooseneck lights, Outside gooseneck lighting, Outside gooseneck, Outside goosenecks, Out side gooseneck light, Out side gooseneck lights, Out side gooseneck lighting, Out side gooseneck, Out side goosenecks, goose neck outside lights, goose neck outside light, goose neck outside lighting, gooseneck outside lights, gooseneck outside light, gooseneck outside 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 gooseneck light, Wall mount gooseneck lights, Wall mount gooseneck lighting, Wall mount gooseneck, Wall mount goosenecks, Wall mounted gooseneck light, Wall mounted gooseneck lights, Wall mounted gooseneck lighting, Wall mounted gooseneck, Wall mounted goosenecks, bleg, fluted lights, jadeite, jadite, jadeite light, jadite light, H-19112, H-19114, H-19116, classic radial shade, classic radial shade gooseneck,	Seaside Gooseneck Light \| Barn Light Electric				275.000000	1530,1602,1615,1697,1820,2001,1897M	761265	Barn Light Electric Company	418	seaside-gooseneck-light	1887/1896/1897	B