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

Query time 0.00136

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 = 65422 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": 6,
      "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
65422 BLE-F-RWS P E C M W A 0 A 1 255.00 1000 0.000 0 0 0 0.00 0 1597845711 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;} 1732205629 Price increased by 10% from $231 to $255 - DC 5/24/22 Y none none 3013,3014,3015,3016 BLE-F 1008383 0 0 0 0 0 0 0 0 1 0.00 0 en Seaside Flush Mount Light <p>A unique decorative addition to a space, the Seaside’s appeal is centered on its whimsical fluted shade. This shade’s inherent playfulness complements casual modern homes and businesses, along with traditional nautical décors and other popular styles. While certain finishes enhance the Seaside’s lively character, other options give the fixture a more refined look.<br /> <br />To imbue a tangible sense of quality into its design, the Seaside is constructed from commercial-grade metal. The shade is diligently crafted entirely by hand in the United States, making it one of the most reliable lights you can find.</p> <p>|break|</p> <ul> <li>Hand built with an American made RLM warehouse shade, this quality industrial pendant lighting is a lasting flush mount option.<span> </span></li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>. </li> </ul> With a wavy shade and fully customizable design, the Seaside brings a stylish edge to homes and businesses. Handcrafted and American made! BLE-F-RWS, Seaside, seaside flush, seaside flush mount, seaside flush mount pendant, seaside flush mount pendant light, seaside flush mount pendant lights, seaside flush mount ceiling pendant, seaside flush mount ceiling light, seaside flush mount ceiling lighting, seaside flush ceiling mount, seaside flush pendant, seaside flush mount light, seaside flush mount lights, seaside flush mount lighting, seaside flush light, seaside flush lights, seaside flush pendant, seaside flush ceiling pendant, seaside flush ceiling mount, seaside flush pendant light, seaside flush ceiling pendant light, seaside flush ceiling mount, The Seaside, The seaside flush, the seaside flush mount, the seaside flush mount pendant, the seaside flush mount pendant light, the seaside flush mount pendant lights, the seaside flush mount ceiling pendant, the seaside flush mount ceiling light, the seaside flush mount ceiling lighting, the seaside flush ceiling mount, the seaside flush pendant, the seaside flush mount light, the seaside flush mount lights, the seaside flush mount lighting, the seaside flush light, the seaside flush lights, the seaside flush pendant, the seaside flush ceiling pendant, the seaside flush ceiling mount, the seaside flush pendant light, the seaside flush ceiling pendant light, the seaside flush ceiling mount, flush mount pendant, flush mount light, flush mount ceiling pendant, flush mount, flush mount light, seaside, H-19112, H-19114, H-19116, classic radial shade, Seaside Flush Mount Light | Barn Light Electric 255.000000 1615,1703,1708,1905,2001,1892M 107540 Barn Light Electric Company 122 seaside-flush-mount-light 1887/1888/1892 B