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

Query time 0.00170

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 = 63827 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": 4,
      "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
63827	BLE-C-RGU	P	E	C	M	A	A	0	A	1	395.00	1000	0.000	0	0	0	0.00	0	1470024000	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;}	1769100563	Price increased by 5% from $196 to $206 - DC 5/27/21 Price increased by 10% from $206 to $227 - DC 5/20/22 Price increased from $227 to $395 - DC 6/29/23	Y		none	none	1057,1058	BLE-C-RGU	{"21970":2,"23060":5,"21941":7,"23553":4,"23857":3}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Static Ring Cord Pendant Light			<p>Industrial styles have surged in popularity, and lighting fixtures matching these sensibilities are now hallmarks of contemporary design. Many interior designers and homeowners incorporate these lights into their projects for their unique character. Enclosing the bulb behind several metal rings, this ceiling light creates a distinctive take on this popular look. A glass guard is offered, but this feature can be left off if you favor a more streamlined aesthetic. The Static Ring is perfect to elevate trendy, modern décors. However, with roots in vintage lighting, this fixture isn't confined to contemporary spaces. Consider the Static Ring’s simple, rugged style for more traditional settings!</p> <p>Customizable with various powder coat colors, glass, guards, and cord options, the Static Ring Pendant matches your individual needs and preferences. The galvanized finish closely adheres to classic industrial lighting, but other options let you infuse your personal tastes into the pendant. Subdued colors — like white, black, and industrial grey — subtly elevate your room’s established décor. More lively finish colors let the Static Ring Pendant stand out, giving your home or business a memorable appearance!</p> <p>\|break\|</p> <ul> <li><span>This light features a d</span><span>ie cast aluminum<span> </span></span><span>guard and optional jelly jar glass!</span></li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>.</li> </ul>		Matching both contemporary and classic sensibilities, the Static Ring Pendant adapts to any space’s needs. Customize this pendant light with a lively finish color! USA made!	BLE-C-RGU-PC, Static Ring Pendant, Static Ring Pendants, Static Ring Pendant Light, Static Ring Pendant Lights, Static Ring Pendant Lighting, Static Ring Ceiling Pendant, Static Ring Ceiling Pendants, Static Ring Ceiling Pendant Light, Static Ring Ceiling Pendant Lights, Static Ring Ceiling Pendant Lighting, Static Ring Ceiling Light, Static Ring Ceiling Lights, Static Ring Ceiling Lighting, Static Ring Light, Static Ring Lights, Static Ring Lighting, Static Ring, Rustic, Rustic Light, Rustic Lights, Rustic Lighting, Rustic Pendant Light, Rustic Pendant Lights, Rustic Pendant Lighting, Rustic Ceiling Pendant, Rustic Ceiling Pendants, Rustic Ceiling Pendant Light, Rustic Ceiling Pendant Lighting, Rustic Ceiling Light, Rustic Ceiling Lights, Rustic Ceiling Lighting, Industrial Light, Industrial Lights, Industrial Lighting, Industrial Pendant Light, Industrial Pendant Lights, Industrial Pendant Lighting, Industrial Ceiling Pendant, Industrial Ceiling Pendants, Industrial Ceiling Light, Industrial Ceiling Lights, Industrial Ceiling Lighting, Static, Rugged Light, Rugged Lights, Rugged Lighting, Rugged Pendant Light, Rugged Pendant Lights, Rugged Pendant Lighting, Rugged Ceiling Pendant, Rugged Ceiling Pendants, Rugged Ceiling Pendant Light, Rugged Ceiling Pendant Lighting, Rugged Ceiling Light, Rugged Ceiling Lights, Rugged Ceiling Lighting, cord hung pendant, cord hung pendants, cord hung pendant light, cord hung pendant lights, cord hung ceiling pendant, cord hung ceiling pendants, cord hung ceiling pendant light, cord hung ceiling pendant lights, cord hung ceiling light, cord hung ceiling lights, cord hung ceiling lighting, rustic cord hung pendant, rustic cord hung pendants, rustic cord hung pendant light, rustic cord hung pendant lights, rustic cord hung ceiling pendant, rustic cord hung ceiling pendants, rustic cord hung ceiling pendant light, rustic cord hung ceiling pendant lights, rustic cord hung ceiling light, rustic cord hung ceiling lights, rustic cord hung ceiling lighting, rugged cord hung pendant, rugged cord hung pendants, rugged cord hung pendant light, rugged cord hung pendant lights, rugged cord hung ceiling pendant, rugged cord hung ceiling pendants, rugged cord hung ceiling pendant light, rugged cord hung ceiling pendant lights, rugged cord hung ceiling light, rugged cord hung ceiling lights, rugged cord hung ceiling lighting, industrial cord hung pendant, industrial cord hung pendants, industrial cord hung pendant light, industrial cord hung pendant lights, industrial cord hung ceiling pendant, industrial cord hung ceiling pendants, industrial cord hung ceiling pendant light, industrial cord hung ceiling pendant lights, industrial cord hung ceiling light, industrial cord hung ceiling lights, industrial cord hung ceiling lighting, ring guard, ring guards, ring guard fixture, ring guard fixtures, ring guard light fixture, ring guard light fixtures, ring, ring fixture, ring fixtures, ring light fixture, ring light fixtures, industrial ring guard, industrial ring guards, ring industrial guard fixtures, ring industrial guard light fixture, industrial ring guard light fixtures, ring industrial guard, ring industrial guards, ring industrial guard fixtures, ring industrial guard light fixture, ring industrial guard light fixtures, Ring guard pendant, ring guard pendants, ring guard ceiling pendant, ring guard ceiling pendants, ring guard pendant light, ring guard pendant lighting, ring guard pendant lights, ring guard wall lights, ring pendant, ring pendants, ring ceiling pendant, ring ceiling pendants, ring pendant light, ring pendant lights, ring pendant lighting, industrial ring guard pendant, industrial ring guard pendants, ring industrial guard ceiling pendant, ring industrial guard ceiling pendants, ring industrial guard pendant light, ring industrial guard pendant lights, ring industrial guard pendant lighting, SPS-0162, SPS 0162, SPS0162, static ring collection, static ring series, the static ring collection, the static ring series, static ring guard collection, static ring guard series, the static ring guard collection, the static ring guard series, static guard collection, static guard series, the static guard collection, the static guard series, corded pendant, corded pendants, corded pendant light, corded pendant lights, corded pendant lighting, corded ceiling pendant, corded ceiling pendants, corded ceiling light, corded ceiling lights, corded ceiling lighting, corded pendant light fixture, corded pendant light fixtures, corded pendant lighting fixture, corded pendant lighting fixtures, corded light fixture, corded light fixtures, corded lighting fixture, corded lighting fixtures, corded,	Static Ring Cord Pendant Light \| Barn Light Electric				395.000000	1542,1701,1761,1889M	231284	Barn Light Electric Company	17	static-ring-pendant-light	1887/1888/1889	B