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

Query time 0.00145

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 = 64352 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
64352 BLE-S-BUB P E C M D A 0 A 1 350.00 1000 0.000 0 0 0 0.00 0 1511970069 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;} 1704807769 Price increased 5% from $303 to $318 - DC 5/27/21 Price increased 10% from $318 to $350 - DC 5/20/22 Y none none 741 BLE-S-BUB {"22879":1} 1008383 0 0 0 0 0 0 0 0 1 0.00 0 en Bubble Stem Pendant Light <p>Successfully channeling midcentury nostalgia, the Bubble Stem Mount honors a classic look while still remaining relevant to the current era’s trends and preferences. To establish its nearly universal appeal, this pendant affixes a vintage-style globe shade and socket to a slender stem mounting. With this simple yet still highly fashionable aesthetic, the Bubble functions as the subtle accent piece needed to elevate residential and commercial décors.</p> <p>A vast selection of finish colors lets the Bubble adapt to any room’s particular requirements. Lively and playful colors, like Sunflower and Teal, enlivens a room, but more subdued neutral tones lets the pendant comply with already established décors. A white shade slightly mutes the light, imbuing a room with warmness, while more light escapes from the clear glass to brighten the space.</p> <p>|break|</p> <ul> <li>Manufactured in the U.S.A. </li> <li><span><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></span></li> <li><span>Read more about the Bubble Stem Mount Pendant over on our <a href="https://www.barnlight.com/inspiration/bubble-lights-embrace-minimalist-design-trends/" target="_blank">blog</a>!</span></li> </ul> Classic midcentury style in a pendant light! Fully customizable with numerous finish options — including brass — and glass options! Free shipping! 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-BUB, midcentury modern, mid century modern, mid-century modern, new downtown and bubble. bubble light, buble, bubel, bubbel, bubble collection, bubble series, the bubble collection, the bubble series, SPS-0103, SPS 0103, SPS0103, bubble lights, bubble light, bubble lighting, bubble light fixture, bubble light fixtures, bubble lighting fixture, bubble light fixtures, Bubble Stem Pendant Light | Barn Light Electric 350.000000 1543,1611,1704,1755,1890M 316531 Barn Light Electric Company 24 bubble-stem-mount-pendant 1887/1888/1890 B