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

Query time 0.00168

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 = 62623 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
62623 BLE-CN-ULM P E C M A A 0 A 1 385.00 1000 0.000 0 0 0 0.00 0 1406865600 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;} 1753285899 Price increased by $38 - DC 5/7/21 37% commercial discount to base price manually added - DC 5/7/21 Price increased by 5% from $305 to $320 - DC 5/26/21 Price increased 10% from $320 to $352 - DC 5/23/22 Price increased from $352 to $385 - DC 10/31/22 Y none none 743,744,745 BLE-CN {"21951":4,"21954":7,"22889":6,"22872":9,"22852":10,"21941":12} 1008383 0 0 0 0 0 0 0 0 1 0.00 0 en Brisbane Chain Pendant Light <p>Our popular Brisbane Pendant gets a stylish upgrade with a cord-and-chain mounting! Featuring a shallow shade over a cast guard, you can create both up and downlight with this industrial pendant light. Easy to customize with shade size, finish, plus guard, glass, cap, and cording options, this light can complement any residential decor or commercial brand.<br /><br />American made from commercial-grade materials, the Brisbane Chain Hung Pendant features Quicklink for easy on-site customization. Hang in pairs over the kitchen island or add a nautical touch to the neighborhood restaurant.</p> <p>|break|</p> <ul> <li>Manufactured and hand-crafted in the USA</li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>!</li> <li>Read more about the Brisbane Chain Hung Pendant Light over on our<span> <a href="https://www.barnlight.com/inspiration/crikey-industrial-lighting-inspired-by-the-land-down-under/" target="_blank">blog</a></span>!</li> </ul> Our Brisbane pendant light now sports a chain for the perfect industrial touch! Create up and down light with our newly styled Brisbane today. the brisbane industrial chain hung pendant, brisbane industrial chain hung pendant, chain hung pendant light, industrial lighting, chain lighting, brisbane, up and down lighting, up and down chain hung light, restaurant lighting, nautical chain lighting, BLE-C-ULMI-PC-CN36, BLE-C-ULMI12-PC-CN36, BLE-C-ULMI14-PC-CN36, BLE-C-ULMI16-PC-CN36, The Brisbane Industrial Chain Hung Pendant, Brisbane Industrial Chain Hung Pendant, The Brisbane Industrial Pendant, The Brisbane Industrial Pendants, Brisbane Industrial Pendant, Brisbane Industrial Pendants, Brisbane industrial pendant, Brisbane industrial pendants, The Brisbane Industrial Pendant, The Brisbane Industrial Pendants, Brisbain, Brisbain industrial pendant, brisbain industrial pendants, Brisbane Industrial Pendant Light, Brisbane Industrial Pendant Lights, Brisbane Industrial Pendant Lighting, BLE-C-ULMI-CN36, Brisbane Chain, Brisbane Chain Light, Brisbane Chain Lights, Brisbane Chain Lighting, BLE-CN-ULM12, BLE-CN-ULM14, BLE-CN-ULM16, Industrial light, industrial lights, industrial lighting, industrial lighting fixture, industrial lighting fixtures, rugged light, rugged lights, rugged lighting, rugged lighting fixture, rugged lighting fixtures, Vintage Industrial light, vintage industrial lights, vintage industrial lighting, vintage industrial lighting fixture, vintage industrial lighting fixtures, cast guard light, cast guard lights, cast guard lighting, cast guard lighting fixture, cast guard lighting fixtures, Queensland collection, queensland lighting, queensland light, queensland lights, queensland lighting, queensland light fixture, queensland light fixtures, Queens land collection, queens land lighting, queens land light, queens land lights, queens land lighting, queens land light fixture, queens land light fixtures, Queensland,  Industrial chain hung pendant, industrial chain hung pendants, industrial chain hung pendant light, Industrial chain hung pendant lights, Industrial chain hung pendant lighting, Industrial chain hung ceiling pendant, Industrial chain hung ceiling pendants, Industrial chain hung pendant light, Industrial chain hung pendant lights, Industrial chain hung pendant lighting, Industrial chain hung ceiling light, Industrial chain hung ceiling lights, Industrial chain hung ceiling lighting, Industrial chain pendant, industrial chain pendants, industrial chain pendant light, Industrial chain pendant lights, Industrial chain pendant lighting, Industrial chain ceiling pendant, Industrial chain ceiling pendants, Industrial chain pendant light, Industrial chain pendant lights, Industrial chain pendant lighting, Industrial chain ceiling light, Industrial chain ceiling lights, Industrial chain ceiling lighting, rugged chain hung pendant, rugged chain hung pendants, rugged chain hung pendant light, rugged chain hung pendant lights, rugged chain hung pendant lighting, rugged chain hung ceiling pendant, rugged chain hung ceiling pendants, rugged chain hung pendant light, rugged chain hung pendant lights, rugged chain hung pendant lighting, rugged chain hung ceiling light, rugged chain hung ceiling lights, rugged chain hung ceiling lighting, rugged chain pendant, rugged chain pendants, rugged chain pendant light, rugged chain pendant lights, rugged chain pendant lighting, rugged chain ceiling pendant, rugged chain ceiling pendants, rugged chain pendant light, rugged chain pendant lights, rugged chain pendant lighting, rugged chain ceiling light, rugged chain ceiling lights, rugged chain ceiling lighting, cast guard chain hung pendant, cast guard chain hung pendants, cast guard chain hung pendant light, cast guard chain hung pendant lights, cast guard chain hung pendant lighting, cast guard chain hung ceiling pendant, cast guard chain hung ceiling pendants, cast guard chain hung pendant light, cast guard chain hung pendant lights, cast guard chain hung pendant lighting, cast guard chain hung ceiling light, cast guard chain hung ceiling lights, cast guard chain hung ceiling lighting, cast guard chain pendant, cast guard chain pendants, cast guard chain pendant light, cast guard chain pendant lights, cast guard chain pendant lighting, cast guard chain ceiling pendant, cast guard chain ceiling pendants, cast guard chain pendant light, cast guard chain pendant lights, cast guard chain pendant lighting, cast guard chain ceiling light, cast guard chain ceiling lights, cast guard chain ceiling lighting, Queensland Chain, Queensland Chain Hung, Queensland Chain Hung Light, Queensland Chain Hung Lights, Queensland Chain Hung Lighting, Queensland Chain Hung Pendant, Queensland Chain Hung Pendants, Queensland Chain Hung Pendant Light, Queensland Chain Hung pendant Lights, Queensland Chain Hung Pendant Lighting, Queensland Chain Hung Ceiling Pendant, Queensland Chain Hung Ceiling Pendants, Queensland Chain Hung Ceiling Light, Queensland Chain Hung Ceiling Lights, Queensland Chain Hung Ceiling Lighting, Queens land Chain Hung, Queens land Chain Hung Light, Queens land Chain Hung Lights, Queens land Chain Hung Lighting, Queens land Chain Hung Pendant, Queens land Chain Hung Pendants, Queens land Chain Hung Pendant Light, Queens land Chain Hung pendant Lights, Queens land Chain Hung Pendant Lighting, Queens land Chain Hung Ceiling Pendant, Queens land Chain Hung Ceiling Pendants, Queens land Chain Hung Ceiling Light, Queens land Chain Hung Ceiling Lights, Queens land Chain Hung Ceiling Lighting, industrial pendant, industrial pendants, industrial ceiling pendant, industrial ceiling pendants, industrial ceiling light, industrial ceiling lights, industrial ceiling lighting, rugged pendant, rugged pendants, rugged ceiling pendant, rugged ceiling pendants, rugged ceiling light, rugged ceiling lights, rugged ceiling lighting, cast guard pendant, cast guard pendants, cast guard ceiling pendant, cast guard ceiling pendants, cast guard ceiling light, cast guard ceiling lights, cast guard ceiling lighting, Queensland pendant, Queensland pendants, queensland pendant light, queensland pendant lights, queensland pendant lighting, queensland ceiling pendant, queensland ceiling pendants, queensland ceiling light, queensland ceiling lights, queensland ceiling lighting, Queens land pendant, Queens land pendants, queens land pendant light, queens land pendant lights, queens land pendant lighting, queens land ceiling pendant, queens land ceiling pendants, queens land ceiling light, queens land ceiling lights, queens land ceiling lighting, Brisbane Series, Brisbane Collection, The Brisbane Series, The Brisbane Collection, Australia collection, Australia Series, the Australia collection, the Australia Series, Queensland Collection, Queensland Series, The Queensland Collection, the Queensland Series, Queens land Collection, Queens land Series, The Queens land Collection, the Queens land Series, SPS-0029, SPS 0029, SPS0029, pastel, Chain barn light, Chain barn lights, Chain barn lighting, mint, Brisbane Chain Pendant Light | Barn Light Electric 385.000000 1541,1619,1643,1700,2002,1891M 1111326 Barn Light Electric Company 41 brisbane-chain-hung-pendant-light 1887/1888/1891 B