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, 
  cscart_discussion.type as discussion_type, 
  company_descriptions.page_title, 
  company_descriptions.meta_description, 
  company_descriptions.meta_keywords 
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 = 'ko' 
  LEFT JOIN cscart_vendor_plan_descriptions ON companies.plan_id = cscart_vendor_plan_descriptions.plan_id 
  AND cscart_vendor_plan_descriptions.lang_code = 'ko' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 5 
  AND cscart_seo_names.type = 'm' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'ko' 
  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 = 5

Query time 0.00924

JSON explain

{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "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 = 'ko')"
    },
    "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 = 'M')"
    },
    "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": ["devmotorsbay.cscart_discussion.thread_id", "const"],
      "rows": 2,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_discussion_posts.`status` = 'A' and trigcond(cscart_discussion.thread_id is not null))"
    },
    "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": ["devmotorsbay.cscart_discussion_posts.post_id"],
      "rows": 1,
      "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 meta_keywords meta_description page_title i18n_company i18n_address i18n_city cp_meta_keywords cp_meta_description cp_page_title cp_st_h1 cp_st_custom_bc 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 pre_moderation pre_moderation_edit pre_moderation_edit_vendors suspend_date grace_period_start last_time_suspended last_debt_notification_time staff_notes sd_ga_tracking_code saved_shippings_state stripe_connect_account_id stripe_connect_account_type messenger messenger_flag plan seo_name seo_path average_rating company_thread_ids discussion_type
5 en <p>I have been gaining experience in the automotive industry since 2002. The beginnings of my business were the trade of car parts and the import, repair and sale of cars. In 2012, after collecting the appropriate capital, I realized my biggest dream and opened a professional car service. Many years of experience in the industry allowed me to gather a team of enthusiasts and professionals and equip the service with modern equipment and tools from renowned suppliers.</p> Platinum, 모터스베이, 중고부품, 수입차부품판매, 수입차중고부품판매, 수입차중고헤드라이트, 사고차부품 I have been gaining experience in the automotive industry since 2002. The beginnings of my business were the trade of car parts and the import, repair and sale of cars. In 2012, after collecting the appropriate capital, I realized my biggest dream and ope Platinum 스토어 | 모터스베이 Platinum Chociszewskiego Leszno Platinum, 모터스베이, 중고부품, 수입차부품판매, 수입차중고부품판매, 수입차중고헤드라이트, 사고차부품 I have been gaining experience in the automotive industry since 2002. The beginnings of my business were the trade of car parts and the import, repair and sale of cars. In 2012, after collecting the appropriate capital, I realized my biggest dream and ope Platinum 스토어 | 모터스베이 A Platinum Chociszewskiego Leszno PL 64-100 heroiz70012@gmail.com +13025870007 none Y 1708829139 0 5 N N N 0 0 0 0 A Y 직접 배송 platinum 5_0