SELECT 
  company_descriptions.*, 
  companies.*, 
  cscart_vendor_plan_descriptions.plan, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  CAST(
    AVG(`prd`.`average_rating`) AS DECIMAL(10, 2)
  ) AS average_rating, 
  prd.reviews_count product_reviews_count 
FROM 
  cscart_companies AS companies 
  LEFT JOIN cscart_company_descriptions AS company_descriptions ON company_descriptions.company_id = companies.company_id 
  AND company_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_vendor_plan_descriptions ON companies.plan_id = cscart_vendor_plan_descriptions.plan_id 
  AND cscart_vendor_plan_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 383 
  AND cscart_seo_names.type = 'm' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  LEFT JOIN cscart_products p ON p.company_id = companies.company_id 
  LEFT JOIN cscart_product_review_prepared_data prd ON p.product_id = prd.product_id 
WHERE 
  companies.company_id = 383

Query time 0.04324

JSON explain

{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "nested_loop": [
      {
        "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": "company_descriptions",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "10",
          "used_key_parts": ["company_id", "lang_code"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_vendor_plan_descriptions",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "10",
          "used_key_parts": ["plan_id", "lang_code"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "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": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_seo_names.`type` = 'm' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "p",
            "access_type": "ALL",
            "rows": 25270,
            "filtered": 100,
            "attached_condition": "p.company_id = 383"
          },
          "buffer_type": "flat",
          "buffer_size": "18Kb",
          "join_type": "BNL",
          "attached_condition": "trigcond(p.company_id = 383)"
        }
      },
      {
        "table": {
          "table_name": "prd",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["dev_db.p.product_id"],
          "rows": 562,
          "filtered": 100,
          "attached_condition": "trigcond(trigcond(p.product_id is not null))"
        }
      }
    ]
  }
}

Result

company_id lang_code company_description terms status company company_search address city city_id district_id ward_id state country zipcode email phone url storefront secure_storefront entry_page redirect_customer countries_list timestamp shippings logos request_user_id request_account_name request_account_data tax_number pre_moderation pre_moderation_edit pre_moderation_edit_vendors plan_id suspend_date grace_period_start last_time_suspended last_debt_notification_time paypal_commerce_platform_account_id stripe_connect_account_id stripe_connect_account_type sended_email_notify ghn_shop_id verify show_homepage messenger messenger_flag plan seo_name seo_path average_rating product_reviews_count
383 vi <p><span>Truong Son Mountain Range has a diverse floral and faunal system. The flora here takes up to half of Vietnam’s endemic plants, including precious medicinal herbs. <br />Born in Hue, the land that absorbs Truong Son’s essence through Bach Ma mountain, pharmacist Dang Binh desires to share the value of plants here by presenting quality health care and pharmaceutical cosmetics products to the customers.<br />After more than a year researching continuously, he founded SAOLA & Làng Hạ (formerly known as Lien Minh Xanh) to provide customers with outstanding products made from natural elements. All the ingredients come from the Hue and Truong Son area, planted, cared for and harvested by local people. <br />In addition to delivering health care and pharmaceutical cosmetics to its customers, the brand creates a stable income for many people by providing them with jobs, as well as contributes to the sustainability of the environment with relevant sourcing and production processes.</span></p> A SAOLA SAOLA 73 Đường Thạch Hãn, Quận 5, phường Thuận Hòa, Thành phố Huế, Thừa Thiên Huế, Việt Nam 46 474 19762 sales@lienminhxanh.com 0919606657 none Y 1647831630 6,4 0 N N N 1 0 0 0 0 0 4800111 Y N A Y Standard lien-minh-xanh-en 4.95