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, 
  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 = '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 = 511 
  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 = 511

Query time 0.00116

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.06"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "companies",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "company_id"
          ],
          "key_length": "4",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "9K"
          },
          "used_columns": [
            "company_id",
            "status",
            "company",
            "lang_code",
            "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",
            "pre_moderation",
            "pre_moderation_edit",
            "pre_moderation_edit_vendors",
            "plan_id",
            "sd_vendor_helper",
            "identifier_facebook_pixel",
            "stripe_connect_account_id",
            "tax_number",
            "facebook_pixel_id",
            "facebook_api_token",
            "handling_time_us",
            "handling_time_uk",
            "handling_time_it",
            "handling_time_in",
            "handling_time_mx",
            "handling_time_es",
            "handling_time_de",
            "handling_time_fr",
            "handling_time_cn",
            "handling_time_ca",
            "handling_time_jp",
            "handling_time",
            "blocked_date",
            "booking_data",
            "stripe_connect_account_type",
            "registered_from_storefront_id",
            "ec_rfq_whatsapp_number",
            "ec_rfq_phone_number",
            "ec_rfq_bhf",
            "ec_rfq_bht",
            "sd_ga_tracking_code",
            "sd_ga_api_secret"
          ]
        }
      },
      {
        "table": {
          "table_name": "company_descriptions",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "company_id",
            "lang_code"
          ],
          "key_length": "10",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "3K"
          },
          "used_columns": [
            "company_id",
            "lang_code",
            "company_description",
            "terms",
            "meta_keywords",
            "meta_description",
            "page_title",
            "i18n_company",
            "i18n_address",
            "i18n_city"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_vendor_plan_descriptions",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "plan_id",
            "lang_code"
          ],
          "key_length": "10",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "792"
          },
          "used_columns": [
            "plan_id",
            "lang_code",
            "plan"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_seo_names",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "dispatch"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "object_id",
            "type",
            "dispatch",
            "lang_code"
          ],
          "key_length": "206",
          "ref": [
            "const",
            "const",
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "name",
            "object_id",
            "type",
            "dispatch",
            "path",
            "lang_code"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion",
          "access_type": "const",
          "possible_keys": [
            "object_id"
          ],
          "key": "object_id",
          "used_key_parts": [
            "object_id",
            "object_type"
          ],
          "key_length": "6",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "2.40",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "thread_id",
            "object_id",
            "object_type"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_posts",
          "access_type": "ref",
          "possible_keys": [
            "thread_id",
            "thread_id_2"
          ],
          "key": "thread_id_2",
          "used_key_parts": [
            "thread_id",
            "status"
          ],
          "key_length": "6",
          "ref": [
            "webmarco.cscart_discussion.thread_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.24",
            "prefix_cost": "3.64",
            "data_read_per_join": "532"
          },
          "used_columns": [
            "post_id",
            "thread_id",
            "status"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_rating",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "thread_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "post_id"
          ],
          "key_length": "3",
          "ref": [
            "webmarco.cscart_discussion_posts.post_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.19",
            "eval_cost": "0.24",
            "prefix_cost": "5.06",
            "data_read_per_join": "19"
          },
          "used_columns": [
            "rating_value",
            "post_id",
            "thread_id"
          ],
          "attached_condition": "<if>(is_not_null_compl(cscart_discussion_rating), (`webmarco`.`cscart_discussion_rating`.`thread_id` = `webmarco`.`cscart_discussion`.`thread_id`), true)"
        }
      }
    ]
  }
}

Result

company_id lang_code company_description terms meta_keywords meta_description page_title 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 pre_moderation pre_moderation_edit pre_moderation_edit_vendors plan_id sd_vendor_helper identifier_facebook_pixel stripe_connect_account_id tax_number facebook_pixel_id facebook_api_token handling_time_us handling_time_uk handling_time_it handling_time_in handling_time_mx handling_time_es handling_time_de handling_time_fr handling_time_cn handling_time_ca handling_time_jp handling_time blocked_date booking_data stripe_connect_account_type registered_from_storefront_id ec_rfq_whatsapp_number ec_rfq_phone_number ec_rfq_bhf ec_rfq_bht sd_ga_tracking_code sd_ga_api_secret plan seo_name seo_path average_rating company_thread_ids
511 sq <p>Staf i kualifikuar prej me shume se 20 vite ne biznes , si dhe me ekperience 6 vjecare ne Amerike . <br></p> <p>Agjensia jone ofron shërbimet brënda dhe jashtë vëndit si :</p> <p>Arkivol &gt; Ofrojme lloje të ndryshme arkivolesh .</p> <p>Kurora arti &gt; Keni mundsi te zgjidhni nga dizajnet dhe format e ndryshme të Kurorave dhe Buqetave qe kemi për ju ne zyrat tona.</p> <p>Autobuze &gt; Mundesojme autobuze per transportin e njerezve pjese te ceremonise .</p> <p>Transport &gt; Ofrojme transportin e te ndjerit .</p> <p>Pergatitje e trupit &gt; Merremi me pergatitjen e te trupit te te ndjerit .</p> <p>Salle ceremoniale &gt;Ne mund t’ju ndihmojmë të krijoni një ceremoni unike kuptimplote për të shprehur individualitetin e vërtetë të të dashurit tuaj.</p> <p>Restorant (Organizimi i Drekes) &gt; Ne merremi me organizimin e ushqimit per te gjithe njerzit pjese te ceremonise .</p> <p>Dokumentacion &gt;&nbsp;Dokumentacion procedurial ne Shtetet e huaja dhe në Gjendjen Civile në Shtetin Shqiptar</p> <p>Suport Staf ne dispozicion &gt; Keni ne cdo rast suportin e stafit tone .</p> funeral agencies, funerals near me, funeral services, coffin, morgue, Funeral Home Ernest, webmarco, website, funeralernest.com, Funeral Directors, funeral industry, person, Cremation, L01705004A, Our agency offers services inside and outside the country. Qualified staff of more than 20 years in business, as well as 6 years of experience in America. Agjensi Funerale ERNEST - Professional Services Agjensi Funerale ERNEST Bulevardi Bajram Curri, 33/3 Tirana A Agjensi Funerale ERNEST Bulevardi Bajram Curri, 33/3 Tirana AL-11 AL 1001 blerim.shguni@yahoo.com 0692092318 https://funeralernest.com/? none Y 1643639336 0 N N N 13 261808336 0 0 0 0 0 0 0 0 0 0 0 0 Vat Excluded 12.20 agjensi-funerale-ernest 511_0