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

Query time 0.00151

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 = 63853 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
63853	BLE-C-PINDY4-VAN36	P	E	C	M	A	A	0	A	1	465.00	1000	0.000	0	0	0	0.00	0	1470888000	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;}	1729182434	Price increased 5% from $335 to $352 - DC 5/28/21 Price increased 10% from $352 to $388 - DC 5/24/22 Price increased from $388 to $465 - DC 6/2/22 39% commercial discount to base price manually added - DC 6/2/22	Y		none	none	585	BLE-C-PINDY4-VAN36	{"22732":2,"21943":2,"23857":4}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Downtown 4-Light Pendant Chandelier			<p>Joining four bare bulb pendants to a single ceiling mounting, this chandelier presents a trendy style as it brightens your home or business. In line with contemporary tastes and traditional sensibilities, our minimalist chandeliers heighten any location’s aesthetic — residential or commercial! Crafted to your specifications, this fixture is available with numerous powder coat finishes and cord styles. Bright, lively options impart pops of color into your bathroom, while the more restrained finishes subtly reinforce your space’s overall appearance.</p> <p>By setting the tone for your décor, this ceiling light puts the finishing touches on remodels and renovation projects! Or swap out your old fixtures with a new chandelier for a quick makeover!</p> <p>\|break\|</p> <ul> <li>Manufactured in the U.S.A.</li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>!</li> <li><span>Adjust the cords to different lengths for a playful style!</span></li> </ul>		Brightening indoor areas and defining their style, our Downtown 4-Light Pendant Chandelier is ideal for both homes and businesses! Customizable with finish colors and cord styles!	The 4-Light Pendant Chandelier, The 4-Light Pendant Chandeliers, The 4 Light Pendant Chandelier, The 4 Light Pendant Chandeliers, 4-Light Pendant Chandelier, 4-Light Pendant Chandeliers, 4 Light Pendant Chandelier, 4 Light Pendant Chandeliers, The 4-Light Pendant Chandelier Light, The 4-Light Pendant Chandelier Lights, The 4-Light Pendant Chandelier Lighting, 4-Light Pendant Chandelier Light, 4-Light Pendant Chandelier Lights, 4-Light Pendant Chandelier Lighting, The 4 Light Pendant Chandelier Light, The 4 Light Pendant Chandelier Lights, The 4 Light Pendant Chandelier Lighting, 4 Light Pendant Chandelier Light, 4 Light Pendant Chandelier Lights, 4 Light Pendant Chandelier Lighting, The 4-Light Pendant, The 4-Light Pendants, The 4 Light Pendant, The 4 Light Pendants, 4-Light Pendant, 4-Light Pendants, 4 Light Pendant, 4 Light Pendants, The 4-Light Pendant Light, The 4-Light Pendant Lights, The 4-Light Pendant Lighting, 4-Light Pendant Light, 4-Light Pendant Lights, 4-Light Pendant Lighting, The 4 Light Pendant Light, The 4 Light Pendant Lights, The 4 Light Pendant Lighting, 4 Light Pendant Light, 4 Light Pendant Lights, 4 Light Pendant Lighting, The 4-Light Ceiling Pendant, The 4-Light Ceiling Pendants, The 4 Light Ceiling Pendant, The 4 Light Ceiling Pendants, 4-Light Ceiling Pendant, 4-Light Ceiling Pendants, 4 Light Ceiling Pendants, 4 Light Ceiling Pendants, The 4-Light Ceiling Pendant Light, The 4-Light Ceiling Pendant Lights, The 4-Light Ceiling Pendant Lighting, 4-Light Ceiling Pendant Light, 4-Light Ceiling Pendant Lights, 4-Light Ceiling Pendant Lighting, The 4 Light Ceiling Pendant Light, The 4 Light Ceiling Pendant Lights, The 4 Light Ceiling Pendant Lighting, 4 Light Ceiling Pendant Light, 4 Light Ceiling Pendant Lights, 4 Light Ceiling Pendant Lighting, The 4-Light Ceiling Light, The 4-Light Ceiling Lights, The 4-Light Ceiling Lighting, The 4 Light Ceiling Light, The 4 Light Ceiling Lights, The 4 Light Ceiling Lighting, 4-Light Ceiling Light, 4-Light Ceiling Lights, 4-Light Ceiling Lighting, 4 Light Ceiling Light, 4 Light Ceiling Lights, 4 Light Ceiling Lighting, The 4-Light Pendant Light, The 4-Light Pendant Lights, The 4-Light Pendant Lighting, The 4 Light Pendant Light, The 4 Light Pendant Lights, The 4 Light Pendant Lighting, 4-Light Pendant Light, 4-Light Pendant Lights, 4-Light Pendant Lighting, 4 Light Pendant Light, 4 Light Pendant Lights, 4 Light Pendant Lighting, Bare Bulb Chandelier, Bare Bulb Chandeliers, Bare Bulb pendant, Bare Bulb pendants, Bare Bulb pendant Light, Bare Bulb pendant lights, Bare Bulb pendant lighting, Bare Bulb ceiling pendant, Bare Bulb ceiling pendants, Bare Bulb ceiling pendant light, Bare Bulb ceiling pendant lights, Bare Bulb ceiling pendant lighting, Bare Bulb Ceiling Light, Bare Bulb ceiling lights, bare bulb ceiling lighting, bare bulb light, bare bulb lights, bare bulb lighting, Cord hung chandelier, cord hung chandeliers, cord hung pendant chandelier, cord hung pendant chandeliers, Multi-light chandelier, multi light chandelier, multi-light chandelier, multi light chandeliers, Multi-pendant chandelier, multi pendant chandelier, multi-pendant chandeliers, multi pendant chandeliers, BLE-WV-PINDY4/VAN24-PC, The four-Light Pendant Chandelier, The four-Light Pendant Chandeliers, The four Light Pendant Chandelier, The four Light Pendant Chandeliers, four-Light Pendant Chandelier, four-Light Pendant Chandeliers, four Light Pendant Chandelier, four Light Pendant Chandeliers, The four-Light Pendant Chandelier Light, The four-Light Pendant Chandelier Lights, The four-Light Pendant Chandelier Lighting, four-Light Pendant Chandelier Light, four-Light Pendant Chandelier Lights, four-Light Pendant Chandelier Lighting, The four Light Pendant Chandelier Light, The four Light Pendant Chandelier Lights, The four Light Pendant Chandelier Lighting, four Light Pendant Chandelier Light, four Light Pendant Chandelier Lights, four Light Pendant Chandelier Lighting, The four-Light Pendant, The four-Light Pendants, The four Light Pendant, The four Light Pendants, four-Light Pendant, four-Light Pendants, four Light Pendant, four Light Pendants, The four-Light Pendant Light, The four-Light Pendant Lights, The four-Light Pendant Lighting, four-Light Pendant Light, four-Light Pendant Lights, four-Light Pendant Lighting, The four Light Pendant Light, The four Light Pendant Lights, The four Light Pendant Lighting, four Light Pendant Light, four Light Pendant Lights, four Light Pendant Lighting, The four-Light Ceiling Pendant, The four-Light Ceiling Pendants, The four Light Ceiling Pendant, The four Light Ceiling Pendants, four-Light Ceiling Pendant, four-Light Ceiling Pendants, four Light Ceiling Pendants, four Light Ceiling Pendants, The four-Light Ceiling Pendant Light, The four-Light Ceiling Pendant Lights, The four-Light Ceiling Pendant Lighting, four-Light Ceiling Pendant Light, four-Light Ceiling Pendant Lights, four-Light Ceiling Pendant Lighting, The four Light Ceiling Pendant Light, The four Light Ceiling Pendant Lights, The four Light Ceiling Pendant Lighting, four Light Ceiling Pendant Light, four Light Ceiling Pendant Lights, four Light Ceiling Pendant Lighting, The four-Light Ceiling Light, The four-Light Ceiling Lights, The four-Light Ceiling Lighting, The four Light Ceiling Light, The four Light Ceiling Lights, The four Light Ceiling Lighting, four-Light Ceiling Light, four-Light Ceiling Lights, four-Light Ceiling Lighting, four Light Ceiling Light, four Light Ceiling Lights, four Light Ceiling Lighting, The four-Light Pendant Light, The four-Light Pendant Lights, The four-Light Pendant Lighting, The four Light Pendant Light, The four Light Pendant Lights, The four Light Pendant Lighting, four-Light Pendant Light, four-Light Pendant Lights, four-Light Pendant Lighting, four Light Pendant Light, four Light Pendant Lights, four Light Pendant Lighting, BLE-WV-PINDY4/VAN36-PC, Pendant chandelier, Pendant Chandeliers, minimalist chandelier, bare bulb chandelier, minimalist pendant, bare bulb pendant, downtown, down town, downtown chandelier, down town chandelier, downtown pendant chandelier, down town pendant chandelier, downtown, down town, down town minimalist, downtown minimalist, downtown pendant, down town pendant, minimalist pendant, downtown, down, town, downtown minimalist pendant, 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, Bare bulb cord, cord bare bulb, bare bulb cord hung, cord hung bare bulb, bare bulb cord hung pendant, bare bulb cord hung pendants, cord hung bare bulb pendant, cord hung bare bulb pendants, cord hung bare bulb ceiling pendant, bare bulb cord hung ceiling pendant, cord hung bare bulb ceiling pendants, bare bulb cord hung ceiling pendants, bare bulb cord hung pendant light, cord hung bare bulb pendant light, cord hung bare bulb pendant lights, bare bulb cord hung pendant lights, cord hung bare bulb pendant lighting, bare bulb cord hung pendant lighting, cord hung bare bulb ceiling light, bare bulb cord hung ceiling light, cord hung bare bulb ceiling lights, bare bulb cord hung ceiling lights, bare bulb cord hung ceiling lighting, cord hung bare bulb ceiling lighting, bare bulb cord mount, cord mount bare bulb, bare bulb cord mount pendant, bare bulb cord mount pendants, cord mount bare bulb pendant, cord mount bare bulb pendants, cord mount bare bulb ceiling pendant, bare bulb cord mount ceiling pendant, cord mount bare bulb ceiling pendants, bare bulb cord mount ceiling pendants, bare bulb cord mount pendant light, cord mount bare bulb pendant light, cord mount bare bulb pendant lights, bare bulb cord mount pendant lights, cord mount bare bulb pendant lighting, bare bulb cord mount pendant lighting, cord mount bare bulb ceiling light, bare bulb cord mount ceiling light, cord mount bare bulb ceiling lights, bare bulb cord mount ceiling lights, bare bulb cord mount ceiling lighting, cord mount bare bulb ceiling lighting, 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-0072, SPS 0072, SPS0072, Downtown Pendant Chandeliers, Downtown Pendant Chandelier Collection, Downtown Pendant Chandelier Series, The Downtown Pendant Chandeliers, The Downtown Pendant Chandelier Collection, The Downtown Pendant Chandelier Series, Down town Pendant Chandeliers, Down town Pendant Chandelier Collection, Down town Pendant Chandelier Series, The Down town Pendant Chandeliers, The Down town Pendant Chandelier Collection, The Down town Pendant Chandelier Series, pindy socket, pindy sockets, pindy cup, pindy cups, pendy socket, pendy sockets, pendy cup, pendy cups, 4 bulb chandelier,	Downtown 4-Light Pendant Chandelier \| Barn Light Electric				465.000000	1544,1661,1748,1794,1829,1894,1893M	259378	Barn Light Electric Company	7	downtown-4-light-pendant-chandelier	1887/1888/1893	B