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

Query time 0.00154

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": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "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": 1,
      "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')"
    },
    "block-nl-join": {
      "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": "cscart_product_prices.product_id = 63855 and cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1)"
      },
      "buffer_type": "flat",
      "buffer_size": "8Kb",
      "join_type": "BNL",
      "attached_condition": "trigcond(1) and trigcond(cscart_product_prices.product_id = 63855 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_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
63855	BLE-C-PINDY6-VAN36	P	E	C	M	A	A	0	A	1	618.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;}	1779997847	Price increased 5% from $424 to $445 - DC 5/28/21 Price increased 10% from $445 to $490 - DC 5/24/22 Price increased from $490 to $618 - DC 6/2/22 39% commercial discount to base price manually added - DC 6/2/22	Y		none	none	583	BLE-C-PINDY6-VAN36	{"22732":2,"21943":2,"23857":4}	1051256	0	0	0	0	0	0	0	0	1	0.00	0	en	Downtown 6-Light Pendant Chandelier			<p>Putting our unique twist on contemporary looks, this fixture hangs six bare bulb pendants from a single canopy. This new look adheres to streamline, minimalist design principles, making our chandeliers perfect for both trendy and classic décors. Bring splashes of color to your bathroom with vibrant finishes for the canopy and charming cord options. Or let this ceiling light blend into previously established décors with the more subdued white, black, and galvanized finishes.</p> <p>Along with this fashionable appearance, this large chandelier performs a practical task. Pouring light onto kitchen tables, islands, and throughout living spaces, the 6-Light Pendant Chandelier brightens any residential location. This chandelier is also intended for commercial locations — perfect to hang above communal tables in restaurants!</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>Learn more about the Downtown 6-Light Pendant Chandelier over on our<span> <a href="/inspiration/modern-sconces-chandelier-lend-curated-look-to-remodel/">blog</a></span>!</li> <li><span>The chandelier’s six pendants are individually adjustable, letting you decide the fixture’s feel.</span></li> </ul>		Spreading light throughout commercial spaces, residential kitchens, and living spaces, this minimalist chandelier joins six pendants to a single ceiling canopy. Customizable with finish colors and cord styles!	BLE-WV-PINDY6/VAN36-PC, The 6-Light Pendant Chandelier, The 6-Light Pendant Chandeliers, The 6 Light Pendant Chandelier, The 6 Light Pendant Chandeliers, 6-Light Pendant Chandelier, 6-Light Pendant Chandeliers, 6 Light Pendant Chandelier, 6 Light Pendant Chandeliers, The 6-Light Pendant Chandelier Light, The 6-Light Pendant Chandelier Lights, The 6-Light Pendant Chandelier Lighting, 6-Light Pendant Chandelier Light, 6-Light Pendant Chandelier Lights, 6-Light Pendant Chandelier Lighting, The 6 Light Pendant Chandelier Light, The 6 Light Pendant Chandelier Lights, The 6 Light Pendant Chandelier Lighting, 6 Light Pendant Chandelier Light, 6 Light Pendant Chandelier Lights, 6 Light Pendant Chandelier Lighting, The 6-Light Pendant, The 6-Light Pendants, The 6 Light Pendant, The 6 Light Pendants, 6-Light Pendant, 6-Light Pendants, 6 Light Pendant, 6 Light Pendants, The 6-Light Pendant Light, The 6-Light Pendant Lights, The 6-Light Pendant Lighting, 6-Light Pendant Light, 6-Light Pendant Lights, 6-Light Pendant Lighting, The 6 Light Pendant Light, The 6 Light Pendant Lights, The 6 Light Pendant Lighting, 6 Light Pendant Light, 6 Light Pendant Lights, 6 Light Pendant Lighting, The 6-Light Ceiling Pendant, The 6-Light Ceiling Pendants, The 6 Light Ceiling Pendant, The 6 Light Ceiling Pendants, 6-Light Ceiling Pendant, 6-Light Ceiling Pendants, 6 Light Ceiling Pendants, 6 Light Ceiling Pendants, The 6-Light Ceiling Pendant Light, The 6-Light Ceiling Pendant Lights, The 6-Light Ceiling Pendant Lighting, 6-Light Ceiling Pendant Light, 6-Light Ceiling Pendant Lights, 6-Light Ceiling Pendant Lighting, The 6 Light Ceiling Pendant Light, The 6 Light Ceiling Pendant Lights, The 6 Light Ceiling Pendant Lighting, 6 Light Ceiling Pendant Light, 6 Light Ceiling Pendant Lights, 6 Light Ceiling Pendant Lighting, The 6-Light Ceiling Light, The 6-Light Ceiling Lights, The 6-Light Ceiling Lighting, The 6 Light Ceiling Light, The 6 Light Ceiling Lights, The 6 Light Ceiling Lighting, 6-Light Ceiling Light, 6-Light Ceiling Lights, 6-Light Ceiling Lighting, 6 Light Ceiling Light, 6 Light Ceiling Lights, 6 Light Ceiling Lighting, The 6-Light Pendant Light, The 6-Light Pendant Lights, The 6-Light Pendant Lighting, The 6 Light Pendant Light, The 6 Light Pendant Lights, The 6 Light Pendant Lighting, 6-Light Pendant Light, 6-Light Pendant Lights, 6-Light Pendant Lighting, 6 Light Pendant Light, 6 Light Pendant Lights, 6 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-PINDY6/VAN26-PC, The six-Light Pendant Chandelier, The six-Light Pendant Chandeliers, The six Light Pendant Chandelier, The six Light Pendant Chandeliers, six-Light Pendant Chandelier, six-Light Pendant Chandeliers, six Light Pendant Chandelier, six Light Pendant Chandeliers, The six-Light Pendant Chandelier Light, The six-Light Pendant Chandelier Lights, The six-Light Pendant Chandelier Lighting, six-Light Pendant Chandelier Light, six-Light Pendant Chandelier Lights, six-Light Pendant Chandelier Lighting, The six Light Pendant Chandelier Light, The six Light Pendant Chandelier Lights, The six Light Pendant Chandelier Lighting, six Light Pendant Chandelier Light, six Light Pendant Chandelier Lights, six Light Pendant Chandelier Lighting, The six-Light Pendant, The six-Light Pendants, The six Light Pendant, The six Light Pendants, six-Light Pendant, six-Light Pendants, six Light Pendant, six Light Pendants, The six-Light Pendant Light, The six-Light Pendant Lights, The six-Light Pendant Lighting, six-Light Pendant Light, six-Light Pendant Lights, six-Light Pendant Lighting, The six Light Pendant Light, The six Light Pendant Lights, The six Light Pendant Lighting, six Light Pendant Light, six Light Pendant Lights, six Light Pendant Lighting, The six-Light Ceiling Pendant, The six-Light Ceiling Pendants, The six Light Ceiling Pendant, The six Light Ceiling Pendants, six-Light Ceiling Pendant, six-Light Ceiling Pendants, six Light Ceiling Pendants, six Light Ceiling Pendants, The six-Light Ceiling Pendant Light, The six-Light Ceiling Pendant Lights, The six-Light Ceiling Pendant Lighting, six-Light Ceiling Pendant Light, six-Light Ceiling Pendant Lights, six-Light Ceiling Pendant Lighting, The six Light Ceiling Pendant Light, The six Light Ceiling Pendant Lights, The six Light Ceiling Pendant Lighting, six Light Ceiling Pendant Light, six Light Ceiling Pendant Lights, six Light Ceiling Pendant Lighting, The six-Light Ceiling Light, The six-Light Ceiling Lights, The six-Light Ceiling Lighting, The six Light Ceiling Light, The six Light Ceiling Lights, The six Light Ceiling Lighting, six-Light Ceiling Light, six-Light Ceiling Lights, six-Light Ceiling Lighting, six Light Ceiling Light, six Light Ceiling Lights, six Light Ceiling Lighting, The six-Light Pendant Light, The six-Light Pendant Lights, The six-Light Pendant Lighting, The six Light Pendant Light, The six Light Pendant Lights, The six Light Pendant Lighting, six-Light Pendant Light, six-Light Pendant Lights, six-Light Pendant Lighting, six Light Pendant Light, six Light Pendant Lights, six Light Pendant Lighting, Pendant chandelier, Pendant Chandeliers, minimalist chandelier, minimalist pendant, bare bulb chandelier, bare bulb pendant, 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-0282, SPS 0282, SPS0282, 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, downtown 6 light pendant	Downtown 6-Light Pendant Chandelier \| Barn Light Electric				618.000000	2010M	292350	Barn Light Electric Company		downtown-6-light-pendant-chandelier	1887/1888/2010	B