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

Query time 0.00135

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 = 64908 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": 8,
      "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
64908 BLE-FSF-DRM P E C M A A 0 A 1 355.00 1000 0.000 0 0 0 0.00 0 1571852723 0 N N N D N P N N N Y 10 1355979600 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;} 1760030087 Price increased by 5% from $246 to $258 - DC 5/27/21 Price increased by 10% from $258 to $284 - DC 5/24/22 Price increased from $284 to $300 - DC 11/3/22 Price increased from $300 to $355 - DM 9/6/23 Y none none 657,658,659,660,3306 BLE-FSF {"22806":3,"21941":12,"22931":9,"22932":9,"22798":10} 1056013 0 0 0 0 0 0 0 0 1 0.00 0 en Drum Schoolhouse Semi-Flush Mount Light <p>Equally decorative and functional, this schoolhouse pendant unites an eye-catching glass shade with a semi-flush mounting. Intended for undersized spaces and areas with low ceilings, the fixture’s mounting lets every room show off an elevated decorative character. Both clear and opaque glass options provide the proper amount of light for any setting.</p> <p>With a wide variety of customizing options, every schoolhouse light can embrace a unique appearance. Choose from several banding patterns, finish colors and materials, and other accessories to create a personalized look for your projects!</p> <p>|break|</p> <ul> <li>Features a customizable glass schoolhouse globe with hand-spun fitter.</li> <li>Made-to-order, learn more about the process<span> </span><a href="https://www.barnlight.com/video-gallery/">here</a>.</li> <li><span>White painted band(s) not recommended if Opaque Glass is selected, as it blends into the glass.</span></li> </ul> An elevated style for any space, our drum schoolhouse semi-flush mount light features a timeless style and is fully customizable! Drum Schoolhouse Flush Mount Light, Drum Schoolhouse Flush Mount, Drum Flush Mount Light, Schoolhouse Flush Mount Light, Schoolhouse Flush Mount, Drum Schoolhouse, Drum School House, Drum Flush Mount, Drum Flush Mount Light, Drum, Drum Schoolhouse, Drum School House, Drum flush mount pendant, Drum Schoolhouse Flush Mount Pendant, Drum Flush Mount pendant light, Drum School House Flush Mount Light, Drum School House Flush Mount, Drum School House Flush Mount Pendant, Schoolhouse, school house, schoolhouse flush, school house flush, schoolhouse pendant, schoolhouse pendant light, schoolhouse ceiling pendant, schoolhouse ceiling pendants, school house pendant, school house pendant light, school house ceiling pendant, school house ceiling pendants, Drum Schoolhouse Semi-flush Mount Light, Drum Schoolhouse Semi-flush Mount, Drum Semi-flush Mount Light, Schoolhouse Semi-flush Mount Light, Schoolhouse Semi-flush Mount, Drum Semi-flush Mount, Drum Semi-flush Mount Light, Drum semi-flush mount pendant, Drum Schoolhouse Semi-flush Mount Pendant, Drum Semi-flush Mount pendant light, Drum School House Semi-flush Mount Light, Drum School House Semi-flush Mount, Drum School House Semi-flush Mount Pendant, BLE-FSF-DRM, semi-flush schoolhouse, semi-flush schoolhouse light, semi flush schoolhouse, semi flush schoolhouse light, Glass drum shade, glass drum shades, drum glass shade, drum glass shades, drum schoolhouse glass, drum schoolhouse glass shade, drum schoolhouse glass shades, schoolhouse drum glass, schoolhouse drum glass shade, schoolhouse drum glass shades, drum school house glass, drum school house glass shade, drum school house glass shades, school house drum glass, school house drum glass shade, school house drum glass shades, Drum schoolhouse light shade, drum schoolhouse light shades, drum schoolhouse lighting shade, drum schoolhouse lighting shades, Drum school house light shade, drum school house light shades, drum school house lighting shade, drum school house lighting shades, Glass drum fixture, glass drum fixtures, glass drum light fixture, glass drum light fixtures, glass drum lighting fixture, glass drum lighting fixtures, glass drum lighting shade, glass drum lighting shades, glass drum light shade, glass drum light shades, drum glass light shade, drum glass light shades, drum glass lighting shade, drum glass lighting shades, Glass schoolhouse shade, glass schoolhouse shades, schoolhouse glass shade, schoolhouse glass shades, glass schoolhouse light shade, glass schoolhouse light shades, glass schoolhouse lighting shades, glass schoolhouse lighting shade, schoolhouse glass light shade, schoolhouse glass light shades, schoolhouse glass lighting shade, schoolhouse glass lighting shades, Glass school house shade, glass school house shades, school house glass shade, school house glass shades, glass school house light shade, glass school house light shades, glass school house lighting shades, glass school house lighting shade, school house glass light shade, school house glass light shades, school house glass lighting shade, school house glass lighting shades, Glass drum pendant, glass drum pendants, drum glass pendant, drum glass pendants, drum glass ceiling pendant, drum glass ceiling pendants, drum glass pendant light, drum glass pendant lights, drum glass pendant lighting, drum glass ceiling light, drum glass ceiling lights, drum glass ceiling lighting, Glass drum ceiling pendant, glass drum ceiling pendants, glass drum pendant light, glass drum pendant lights, glass drum pendant lighting, glass drum ceiling light, glass drum ceiling lights, glass drum ceiling lighting, Glass schoolhouse pendant, glass schoolhouse pendants, glass schoolhouse ceiling pendant, glass schoolhouse ceiling pendants, glass schoolhouse pendant light, glass schoolhouse pendant lights, glass schoolhouse pendant lighting, glass schoolhouse ceiling light, glass schoolhouse ceiling lights, glass schoolhouse ceiling lighting, Schoolhouse glass pendant, schoolhouse glass pendants, schoolhouse glass ceiling pendant, schoolhouse glass ceiling pendants, schoolhouse glass pendant light, schoolhouse glass pendant lights, schoolhouse glass pendant lighting, schoolhouse glass ceiling light, schoolhouse glass ceiling lights, schoolhouse glass ceiling lighting, Glass school house pendant, glass school house pendants, glass school house ceiling pendant, glass school house ceiling pendants, glass school house pendant light, glass school house pendant lights, glass school house pendant lighting, glass school house ceiling light, glass school house ceiling lights, glass school house ceiling lighting, School house glass pendant, school house glass pendants, school house glass ceiling pendant, school house glass ceiling pendants, school house glass pendant light, school house glass pendant lights, school house glass pendant lighting, school house glass ceiling light, school house glass ceiling lights, school house glass ceiling lighting, Drum glass schoolhouse flush mount pendant, drum glass schoolhouse flush mount pendants, drum glass schoolhouse flush mount ceiling pendant, drum glass schoolhouse flush mount ceiling pendants, drum glass schoolhouse flush mount pendant light, drum glass schoolhouse flush mount pendant lights, drum glass schoolhouse flush mount pendant lighting, drum glass schoolhouse flush mount ceiling light, drum glass schoolhouse flush mount ceiling lights, drum glass schoolhouse ceiling lighting, glass drum schoolhouse flush mount pendant, glass drum schoolhouse flush mount pendants, glass drum schoolhouse flush mount ceiling pendant, glass drum schoolhouse flush mount ceiling pendants, glass drum schoolhouse flush mount pendant light, glass drum schoolhouse flush mount pendant lights, glass drum schoolhouse flush mount pendant lighting, glass drum schoolhouse flush mount ceiling light, glass drum schoolhouse flush mount ceiling lights, glass drum schoolhouse ceiling lighting, Drum glass school house flush mount pendant, drum glass school house flush mount pendants, drum glass school house flush mount ceiling pendant, drum glass school house flush mount ceiling pendants, drum glass school house flush mount pendant light, drum glass school house flush mount pendant lights, drum glass school house flush mount pendant lighting, drum glass school house flush mount ceiling light, drum glass school house flush mount ceiling lights, drum glass school house ceiling lighting, glass drum school house flush mount pendant, glass drum school house flush mount pendants, glass drum school house flush mount ceiling pendant, glass drum school house flush mount ceiling pendants, glass drum school house flush mount pendant light, glass drum school house flush mount pendant lights, glass drum school house flush mount pendant lighting, glass drum school house flush mount ceiling light, glass drum school house flush mount ceiling lights, glass drum school house ceiling lighting, Schoolhouse collection, schoolhouse series, the schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Drum Schoolhouse collection, Drum schoolhouse series, the Drum schoolhouse collection, the schoolhouse series, School house collection, school house series, the school house collection, the school house series, Drum series, drum collection, SPS-0486, SPS0486, SPS 0486, Flush mount school house, Flush mount schoolhouse, schoolhouse drum glass, glass schoolhouse drum, glass schoolhouse drum shade, glass schoolhouse drum shades, glass schoolhouse drum shade, glass schoolhouse drum shades, schoolhouse drum flush mount, drum schoolhouse flush mount, schoolhouse drum flush mount pendant, drum schoolhouse flush mount pendants, schoolhouse drum flush mount light, drum schoolhouse flush mount lights, schoolhouse drum flush mount lighting, school house drum glass, glass school house drum, glass school house drum shade, glass school house drum shades, glass school house drum shade, glass school house drum shades, school house drum flush mount, drum school house flush mount, school house drum flush mount pendant, drum school house flush mount pendants, school house drum flush mount light, drum school house flush mount lights, school house drum flush mount lighting, drum schoolhouse semi flush, drum schoolhouse semi-flush, Small mount lighting, Small mount light, Small mount lights, foyer light, foyer lights, foyer lighting, school light, school lights, school lighting, school, ceiling flush mount, milk glass ceiling mount, striped schoolhouse, striped schoolhouse light, drum semi flush, semi flush drum, semi flush drum light, drum light semi flush, Drum Schoolhouse Semi-Flush Mount Light | Barn Light Electric 355.000000 1547,1607,1703,1757,1827,1883,1892,2002M 287037 Barn Light Electric Company 25 drum-schoolhouse-semi-flush-mount-light 2002 B