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

Query time 0.00184

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 = 64963 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": 5,
      "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
64963 BLE-S-GLB P E C M D A 0 A 1 340.00 1000 0.000 0 0 0 0.00 0 1578324857 0 N N N D N R N N N Y 10 0 N 0 0 0 0 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;} 1743095305 Price increased 5% from $250 to $262 - DC 5/27/21 Price increased by $37 - DC 3/18/22 Price increased 10% from $299 to $329 - DC 5/20/22 Price increased from $329 to $340 - DC 11/8/22 Y none none 1214 BLE-S {"23110":3,"22684":4} 1008383 0 0 0 0 0 0 0 0 1 0.00 0 en Glass Ball Stem Pendant Light <div> <p><span lang="en-US"><span>Effortlessly expressing styles from midcentury modern to traditional farmhouse, the Glass Ball Pendant is a natural addition to every setting. A round glass shade and a simple stem mounting are at the heart of this pendant’s design. When installed throughout a décor as either an accent piece or focal point, this fixture helps rooms feel elevated and stylish.</span></span><span> </span></p> </div> <div> <p><span lang="en-US"><span>Various finish colors and options including powder coat, copper, and brass determine the Glass Ball Pendant’s aesthetic. Several sizes and two glass options provide the proper dimensions for every space. </span></span><span> </span></p> </div> <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> <div>Looking for replacement glass for your Glass Ball Pendant? Click <a href="/accessories/hardware/glass-ball-globes/">here!</a></div> </li> </ul> midcentury, mid-century, mid century, midcentury pendant, mid-century pendant, mid century pendant, midcentury pendant light, midcentury pendant lights, midcentury pendant lighting, mid-century pendant light, mid-century pendant lights, mid-century pendant lighting, mid century pendant light, mid century pendant lights, mid century pendant lighting, midcentury modern, mid century modern, mid-century modern, new downtown, BLE-S-GLB8, BLE-S-GLB12, BLE-S-GLB16, Glass sphere, glass spheres, glass sphere shade, glass sphere shades, sphere glass, sphere glass shade, sphere glass shades, glass sphere light, glass sphere lights, glass sphere lighting, sphere glass light, sphere glass lights, sphere glass lighting, glass sphere pendant, glass sphere pendants, glass sphere ceiling pendant, glass sphere ceiling pendants, glass sphere pendant light, glass sphere pendant lights, glass sphere pendant lighting, glass sphere ceiling light, glass sphere ceiling lights, glass sphere ceiling lighting, Sphere glass pendant, sphere glass pendants, sphere glass ceiling pendant, sphere glass ceiling pendants, sphere glass pendant light, sphere glass pendant lights, sphere glass pendant lighting, sphere glass ceiling light, sphere glass ceiling lights, sphere glass ceiling lighting, glass sphere light fixture, glass sphere light fixtures, glass sphere lighting fixture, glass sphere lighting fixtures, Round glass shade, round glass shades, round glass shade light, round glass shade lights, round glass shade lighting, Glass round shade, glass round shades, glass round shade light, glass round shade lights, glass round shade lighting, round glass light, round glass lights, round glass lighting, glass round light, glass round lights, glass round lighting, Glass round light, glass round lights, glass round lighting, Round glass shade pendant, round glass shade pendants, glass round shade pendant, glass round shade pendants, round glass pendant, round glass pendants, glass round pendant, glass round pendants, glass round ceiling pendant, glass round ceiling pendants, round glass ceiling pendant, round glass ceiling pendants, glass round pendant light, round glass pendant light, glass round pendant lights, round glass pendant lights, glass round pendant lighting, round glass pendant lighting, glass round ceiling light, round glass ceiling light, glass round ceiling lights, round glass ceiling lights, round glass ceiling lighting, glass round ceiling lighting, Glass globe shade, glass globe shades, glass globe shade light, glass globe shade lights, glass globe shade lighting, glass globe shade fixture, glass globe shade fixtures, glass globe shade light fixture, glass globe shade light fixtures, glass globe shade lighting fixture, glass globe shade lighting fixtures, glass globe pendant, glass globe pendants, glass globe ceiling pendant, glass globe ceiling pendants, glass globe pendant light, glass globe pendant lights, glass globe pendant lighting, glass globe ceiling light, glass globe ceiling lights, glass globe ceiling lighting, Globe shade, globe shades, globe shade light, globe shade lights, globe shade lighting, globe shade fixture, globe shade fixtures, globe shade light fixture, globe shade light fixtures, globe shade lighting fixture, globe shade lighting fixtures, globe light shade, globe light shades, globe light fixtures, globe light fixture, globe lighting fixture, globe lighting fixtures, Globe shade pendant, Globe shade pendants, Globe shade ceiling pendant, Globe shade ceiling pendants, globe pendant, globe pendants, globe ceiling pendant, globe ceiling pendants, globe pendant, globe pendants, globe pendant light, globe pendant lights, globe pendant lighting, globe ceiling pendant, globe ceiling pendants, globe ceiling light, globe ceiling lights, globe ceiling lighting, glass ball pendant, glass ball pendants, glass ball pendant light, glass ball pendant lights, glass ball pendant lighting, glass ball stem mount, glass ball stem mounts, glass ball stem mount light, glass ball stem mount lights, glass ball stem mount lighting, glass ball stem mount pendant, glass ball stem mount pendants, glass ball stem mount pendant light, glass ball stem mount pendant lights, glass ball stem mount pendant lighting, glass ball stem mount ceiling pendant, glass ball stem mount ceiling pendants, glass ball stem mount ceiling light, glass ball stem mount ceiling lights, glass ball stem mount ceiling lighting, BLE-S-GLB, Glass Ball collection, Glass Ball Series, the glass ball collection, the glass ball series, SPS 0076, SPS0076, SPS-0076, glass shade, glass ball shade, glass ball shades, glass ball light, glass ball lights, glass ball lighting, stems, Stem pendant lights, stem light mount, stem light mounts, stem, opaque glass pendant, clear glass pendant, glb, glbcl, Glass Ball Stem Pendant Light | Barn Light Electric 340.000000 1543,1704,1765,2002,1890M 140053 Barn Light Electric Company 51 glass-ball-stem-mount-pendant-light 1887/1888/1890 B