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 = 64351 
  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 = 64351 
  AND (
    companies.status = 'A' 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00118

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 = 64351 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 = 64351 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
64351 BLE-S-CH028 P E C M D A 0 A 1 1400.00 1000 0.000 0 0 0 0.00 0 1511969068 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 reduced from $1452 to $949 per SG - DC 11/11/19 Price increased 5% from $949 to $997 - DC 5/28/21 Price increased 10% from $997 to $1097 - DC 5/24/22 Price increased by $203 to account for shipping - DC 11/17/22 Y none none 742 BLE-S-CH028 {"22879":1,"21970":2} 1008383 0 0 0 0 0 0 0 0 1 0.00 0 en Bubble 5-Light Chandelier <p>With a style circa the midcentury, the Bubble embraces the playfulness that often epitomizes contemporary design. Aesthetically, this chandelier consolidates multiple vintage-style sockets, each concealed behind a spherical glass shade, and slim metal arms to articulate the period’s eye-catching style — a look suitable for both residential commercial settings. Subtle touches of lively color lets the chandelier grab attention in sparsely decorated spaces, while more restrained finishes helps the Bubble fit into décors abundant with textures and colors.</p> <p>From dining rooms and kitchens to living rooms and entryways, the Bubble’s clear glass shades offers more brightness, often functioning as a space’s primary source of light, while the white tint shade slightly mutes the light for calm, warm rooms. In commercial venues, the inclusion of this chandelier helps businesses feel current and trendy!</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><span>Read more about the Bubble 5-Light Chandelier over on our <a href="https://www.barnlight.com/inspiration/bubble-lights-embrace-minimalist-design-trends/" target="_blank">blog</a>!</span></li> </ul> A fashionable mid century chandelier with an imaginative design! Customizable with various finish colors and glass options! Made in the USA! The Bubble Chandelier, Bubble Chandelier, The Bubble Chandeliers, Bubble Chandeliers, The Bubble 5-Light Chandelier, The Bubble 5-Light Chandeliers, Bubble 5-Light Chandelier, Bubble 5-Light Chandeliers, The Bubble 5 Light Chandelier, The Bubble 5 Light Chandeliers, Bubble 5 Light Chandelier, Bubble 5 Light Chandeliers, 5-Light Chandelier, 5-Light Chandeliers, 5 Light Chandelier, 5 Light Chandelier, Bubble, The Bubble, midcentury chandelier, mid-century chandelier, mid century chandelier, midcentury chandeliers, mid-century chandeliers, mid century chandeliers, modern chandelier, modern chandeliers, BLE-S-CH028-PC, The Bubble Stem Mount Pendant, The Bubble Stem Mount Pendant Light, The Bubble Stem Mount Pendant Lights, The Bubble Stem Mount Pendant Lighting, The Bubble Stem Mount Ceiling Pendant, The Bubble Stem Mount Ceiling Pendants, The Bubble Stem Mount Ceiling Light, The Bubble Stem Mount Ceiling Lights, The Bubble Stem Mount Ceiling Lighting, Bubble Stem Mount Pendant, Bubble Stem Mount Pendant Light, Bubble Stem Mount Pendant Lights, Bubble Stem Mount Pendant Lighting, Bubble Stem Mount Ceiling Pendant, Bubble Stem Mount Ceiling Pendants, Bubble Stem Mount Ceiling Light, Bubble Stem Mount Ceiling Lights, Bubble Stem Mount Ceiling Lighting, The Bubble Stem Pendant, The Bubble Stem Pendant Light, The Bubble Stem Pendant Lights, The Bubble Stem Pendant Lighting, The Bubble Stem Ceiling Pendant, The Bubble Stem Ceiling Pendants, The Bubble Stem Ceiling Light, The Bubble Stem Ceiling Lights, The Bubble Stem Ceiling Lighting, Bubble Stem Pendant, Bubble Stem Pendant Light, Bubble Stem Pendant Lights, Bubble Stem Pendant Lighting, Bubble Stem Ceiling Pendant, Bubble Stem Ceiling Pendants, Bubble Stem Ceiling Light, Bubble Stem Ceiling Lights, Bubble Stem Ceiling Lighting, The Bubble Pendant, The Bubble Pendant Light, The Bubble Pendant Lights, The Bubble Pendant Lighting, The Bubble Ceiling Pendant, The Bubble Ceiling Pendants, The Bubble Ceiling Light, The Bubble Ceiling Lights, The Bubble Ceiling Lighting, Bubble Pendant, Bubble Pendant Light, Bubble Pendant Lights, Bubble Pendant Lighting, Bubble Ceiling Pendant, Bubble Ceiling Pendants, Bubble Ceiling Light, Bubble Ceiling Lights, Bubble Ceiling Lighting, Midcentury pendant, mid-century pendant, mid century pendant, Midcentury pendant light, mid-century pendant light, mid century pendant light, Midcentury pendant lights, mid-century pendant lights, mid century pendant lights, Midcentury pendant lighting, mid-century pendant lighting, mid century pendant lighting, Midcentury ceiling pendant, mid-century ceiling pendant, mid century ceiling pendant, Midcentury ceiling pendants, mid-century ceiling pendants, mid century ceiling pendants, Midcentury ceiling light, mid-century ceiling light, mid century ceiling light, Midcentury ceiling lights, mid-century ceiling lights, mid century ceiling lights, Midcentury ceiling lighting, mid-century ceiling lighting, mid century ceiling lighting, Midcentury light, mid-century light, mid century light, Midcentury lights, mid-century lights, mid century lights, Midcentury lighting, mid-century lighting, mid century lighting, Modern pendant, modern pendants, modern pendant light, modern pendant lights, modern pendant lighting, modern ceiling pendant, modern ceiling pendants, modern ceiling light, modern ceiling lights, modern ceiling lighting, contemporary pendant, contemporary pendants, contemporary ceiling pendant, contemporary ceiling pendants, contemporary pendant light, contemporary pendant lights, contemporary pendant lighting, contemporary ceiling light, contemporary ceiling lights, contemporary ceiling lighting, midcentury light, mid-century lights, mid century lighting, modern light, modern lights, modern lighting, contemporary light, contemporary lights, contemporary lighting, BLE-S-CH028-PC, contemporary chandelier, contemporary chandeliers, midcentury modern, mid century modern, mid-century modern, new downtown and bubble, bubble light, bubble, buble, bubbel, bubble collection, bubble series, the bubble collection, the bubble series, SPS-0003, SPS 0003, SPS0003, bubble lights, bubble light, bubble lighting, bubble light fixture, bubble light fixtures, bubble lighting fixture, bubble light fixtures, bubble light chandelier, Bubble 5-Light Chandelier | Barn Light Electric 1400.000000 2010M 396407 Barn Light Electric Company bubble-5-light-chandelier 1887/1888/2010 B