SELECT 
  company_descriptions.*, 
  companies.*, 
  cscart_vendor_plan_descriptions.plan, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  AVG(
    cscart_discussion_rating.rating_value
  ) AS average_rating, 
  CONCAT(
    companies.company_id, 
    '_', 
    IF (
      cscart_discussion_rating.thread_id, 
      cscart_discussion_rating.thread_id, 
      '0'
    )
  ) AS company_thread_ids 
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 = 945 
  AND cscart_seo_names.type = 'm' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  LEFT JOIN cscart_discussion ON cscart_discussion.object_id = companies.company_id 
  AND cscart_discussion.object_type = 'M' 
  LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id 
  AND cscart_discussion_posts.status = 'A' 
  LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id 
  AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id 
WHERE 
  companies.company_id = 945

Query time 0.00055

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00703764,
    "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"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00183779,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_seo_names.`type` = 'm' 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"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001693605,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion.object_type = 'M')",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_posts",
          "access_type": "ref",
          "possible_keys": ["thread_id", "thread_id_2"],
          "key": "thread_id_2",
          "key_length": "6",
          "used_key_parts": ["thread_id", "status"],
          "ref": ["u508912950_dev_2025.cscart_discussion.thread_id", "const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001792605,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion_posts.`status` = 'A' and trigcond(cscart_discussion.thread_id is not null))",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_rating",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "thread_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["post_id"],
          "ref": ["u508912950_dev_2025.cscart_discussion_posts.post_id"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00171364,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion_rating.thread_id = cscart_discussion.thread_id and trigcond(cscart_discussion_posts.post_id is not null))"
        }
      }
    ]
  }
}

Result

company_id lang_code company_description terms i18n_company i18n_address i18n_city status company address city 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 registered_from_storefront_id plan_id suspend_date grace_period_start last_time_suspended last_debt_notification_time paypal_commerce_platform_account_id pre_moderation pre_moderation_edit pre_moderation_edit_vendors stripe_connect_account_id stripe_connect_account_type nt_commission_include_price plan seo_name seo_path average_rating company_thread_ids
945 en <span>ADEN Cosmetics has been dedicated to delivering professional-quality makeup at an affordable price. Our mission is to empower your beauty journey with products that are not only reliable and easy to apply but also infused with elegance and practical design. What truly sets us apart is our commitment to innovation, quality, and our vibrant community of trendsetters.</span> Aden Cosmetics Malta 85, Triq Dun Marju Qormi A Aden Cosmetics Malta 85, Triq Dun Marju Qormi MT teststefan@adencosmetics.mt +35699012023 none Y 1739902627 18 0 a:4:{s:14:"company_fields";a:3:{i:37;s:6:"Stefan";i:38;s:6:"Zerafa";i:52;s:10:"MT31585319";}s:15:"admin_firstname";s:6:"Stefan";s:14:"admin_lastname";s:6:"Zerafa";s:6:"fields";a:0:{}} 1 6 0 0 0 0 N N N acct_1R9szvRr1h2YEq97 S N Free aden-cosmetics-malta 945_0