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(
    CASE WHEN (
      cscart_products_categories.link_type = 'M'
    ) THEN CONCAT(
      cscart_products_categories.category_id, 
      'M'
    ) ELSE cscart_products_categories.category_id END 
    ORDER BY 
      cscart_categories.storefront_id IN (0, 1) DESC, 
      (
        cscart_products_categories.link_type = 'M'
      ) DESC, 
      cscart_products_categories.category_position ASC, 
      cscart_products_categories.category_id ASC
  ) as category_ids, 
  GROUP_CONCAT(
    CASE WHEN (
      cscart_products_tag_categories.link_type = 'M'
    ) THEN CONCAT(
      cscart_products_tag_categories.category_id, 
      'M'
    ) ELSE cscart_products_tag_categories.category_id END 
    ORDER BY 
      cscart_categories.storefront_id IN (0, 1) DESC, 
      (
        cscart_products_tag_categories.link_type = 'M'
      ) DESC, 
      cscart_products_tag_categories.category_position ASC, 
      cscart_products_tag_categories.category_id ASC
  ) as category_tag_ids, 
  popularity.total as popularity, 
  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, 
  cscart_product_review_prepared_data.average_rating average_rating, 
  cscart_product_review_prepared_data.reviews_count product_reviews_count, 
  ps.sold as sold 
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_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 
  LEFT JOIN cscart_products_tag_categories ON cscart_products_tag_categories.product_id = cscart_products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 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 = 165 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 25316 
  AND cscart_seo_names.type = 'p' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id 
  AND cscart_discussion.object_type = 'P' 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = cscart_products.product_id 
  AND cscart_product_review_prepared_data.storefront_id = 0 
  LEFT JOIN cscart_product_sold ps ON cscart_products.product_id = ps.product_id 
WHERE 
  cscart_products.product_id = 25316 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.02242

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "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": "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": 0,
          "filtered": 0,
          "unique_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "cscart_product_prices",
          "access_type": "ref",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id"
          ],
          "key": "product_id",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(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": "PRIMARY",
          "key_length": "11",
          "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": "companies",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["company_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(companies.`status` = 'A')"
        }
      },
      {
        "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": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_products_tag_categories",
          "access_type": "ref",
          "possible_keys": ["pt"],
          "key": "pt",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "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": ["dev_db.cscart_products_categories.category_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "cscart_categories.storefront_id in (0,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": "ref",
          "possible_keys": ["PRIMARY", "dispatch"],
          "key": "PRIMARY",
          "key_length": "206",
          "used_key_parts": ["object_id", "type", "dispatch", "lang_code"],
          "ref": ["const", "const", "const", "const"],
          "rows": 2,
          "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": "const",
          "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')"
        }
      },
      {
        "table": {
          "table_name": "cscart_product_review_prepared_data",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "7",
          "used_key_parts": ["product_id", "storefront_id"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "rows": 12545,
            "filtered": 100,
            "attached_condition": "ps.product_id = 25316"
          },
          "buffer_type": "flat",
          "buffer_size": "126Kb",
          "join_type": "BNL",
          "attached_condition": "trigcond(ps.product_id = 25316)",
          "materialized": {
            "query_block": {
              "select_id": 2,
              "filesort": {
                "sort_key": "count(cscart_order_details.product_id) desc",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "cscart_order_details",
                        "access_type": "index",
                        "key": "o_k",
                        "key_length": "6",
                        "used_key_parts": ["order_id", "product_id"],
                        "rows": 12545,
                        "filtered": 100,
                        "using_index": true
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  }
}

Result

product_id product_code product_type status company_id list_price amount weight length width height shipping_freight low_avail_limit timestamp updated_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 is_make_to_order make_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 facebook_obj_type parent_product_id buy_now_url is_stock_split_by_warehouses is_fragile lang_code product shortname short_description full_description meta_keywords meta_description search_words page_title age_warning_message promo_text specification special_note punch_line selection chus_note about_brand why_chus purchasing_notice note_from_brand is_sync_seo_url price category_ids category_tag_ids popularity company_name sales_amount seo_name seo_path discussion_type average_rating product_reviews_count sold
25316 10120_C687 P A 110 0 0 2000.000 0 0 0 0.00 0 1663870866 1724830065 0 N N N B N N N N Y 10 N - 0 N N 0 default a:5:{s:16:"min_items_in_box";i:1;s:16:"max_items_in_box";i:1;s:10:"box_length";i:35;s:9:"box_width";i:25;s:10:"box_height";i:25;} 0 N N en Vintage Storage Bag, Along With Woody Frame (Small Size), Easily Foldable and Portable, Durable, Home Decor, Handcrafted Product <p>Organize your essentials and décorate your house with this rustic and vintage storage bag. With compact and flexible design, you can simply fold it up and carry it anywhere.</p> <p>The product is made of canvas so it’s durable for daily usage</p> , Vintage Storage Bag, Along With Woody Frame Small Size, Easily Foldable and Portable, Durable, Home Decor, Handcrafted Product Vintage Storage Bag, Along With Woody Frame (Small Size), Sewing Art Decor Studio - CHUS <p>Size: 17*18*12cm</p> <p>Material: Cotton canvas, wood</p> <p>Make sure to wash it at cool temperature for both washing machine and handwashing</p> Space-saving | Convenient | Multipurpose | Eco-friendly | Cotton Canvas | Vietnamese Handicrafts | Authentic Design <p>1. Vintage Storage Bag Along With Woody Frame - Cream White Color</p> <p>2. Vintage Storage Bag Along With Woody Frame - Gray Color</p> <p>Sewing art décor, a local Vietnamese handicraft brand was founded by two young enthusiastic artists. They have been passionate about Vietnamese handicraft products from a young age. But soon they realized there was a saturation for these products in the Vietnamese market and they were not too impressed by the quality of the existing products.</p> <p>This was a turning point for the two talented founders and they started sewing art décor. They started with “art-doll” or commonly known as Chibi art dolls. They aim to provide exclusive service to their customers with their unique design and customizations. All products at Sewing Art are eco-friendly and sustainable household items.</p> <p>Slowly and steadily Sewing Art has made a name for themselves in the domestic décor industry. They aspire to set a benchmark for eco-friendly handcrafted products and redefine art-doll industry in Vietnam</p> <p>&nbsp;</p> a:4:{s:17:"rs_brand_producer";a:5:{i:0;s:12:"Eco-friendly";i:1;s:23:"Supports local artisans";i:2;s:24:"Promote art-doll artform";i:3;s:0:"";i:4;s:0:"";}s:14:"rs_ingredients";a:5:{i:0;s:12:"Cloth & wood";i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";}s:14:"rs_manufacture";a:5:{i:0;s:13:"100 %Handmade";i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";}s:17:"rs_certifications";a:5:{i:0;s:0:"";i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";}} <p>Warranty:</p> <p>Offer to replace or repair the product free of charge due to reasons related to raw materials, sewing defect in the first 2 years</p> 1 240000.0000 165M 8070 Sewing Art Decor Studio vintage-storage-bag-along-with-woody-frame-small-size 101/165 D 0