SELECT 
  shop_policies.*, 
  return_policy_descriptions.policy AS return_policy, 
  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 = 189 
  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 
  LEFT JOIN cscart_return_policies AS return_policies ON return_policies.object_id = companies.company_id 
  AND return_policies.object_type = 'company' 
  LEFT JOIN cscart_return_policy_descriptions AS return_policy_descriptions ON return_policy_descriptions.policy_id = return_policies.policy_id 
  AND return_policy_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_shop_policies AS shop_policies ON shop_policies.company_id = companies.company_id 
  AND shop_policies.lang_code = 'en' 
WHERE 
  companies.company_id = 189

Query time 0.00117

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_discussion_posts",
          "access_type": "system",
          "possible_keys": [
            "thread_id",
            "thread_id_2"
          ],
          "rows_examined_per_scan": 0,
          "rows_produced_per_join": 1,
          "filtered": "0.00",
          "const_row_not_found": true,
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "448"
          },
          "used_columns": [
            "post_id",
            "thread_id",
            "status"
          ]
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_rating",
          "access_type": "system",
          "possible_keys": [
            "PRIMARY",
            "thread_id"
          ],
          "rows_examined_per_scan": 0,
          "rows_produced_per_join": 1,
          "filtered": "0.00",
          "const_row_not_found": true,
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "rating_value",
            "post_id",
            "thread_id"
          ]
        }
      },
      {
        "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.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "15K"
          },
          "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",
            "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",
            "paypal_commerce_platform_account_id",
            "discreet_shipping",
            "shipstation_enabled",
            "shipstation_fulfillment",
            "shipstation_api_key",
            "shipstation_api_secret",
            "shipstation_api_connected",
            "shipstation_webhook_id",
            "shipstation_store_id",
            "shipstation_warehouse_name",
            "shipstation_warehouse_street1",
            "shipstation_warehouse_street2",
            "shipstation_warehouse_street3",
            "shipstation_warehouse_country",
            "shipstation_warehouse_state",
            "shipstation_warehouse_city",
            "shipstation_warehouse_postal_code",
            "shipstation_warehouse_phone",
            "shipstation_warehouse_residential",
            "shipstation_warehouse_id",
            "shipengine_enabled",
            "shipengine_api_key",
            "shipengine_api_connected",
            "shipengine_webhook_id",
            "shipengine_account_id",
            "shipengine_external_account_id",
            "shipengine_api_key_id",
            "discreet_shipping_shop_name",
            "vacation_mode",
            "vacation_start_date",
            "vacation_end_date",
            "tagline",
            "shopify_token",
            "shopify_link_app",
            "shopify_api_key",
            "shopify_api_secret",
            "stripe_connect_account_id",
            "stripe_connect_account_type",
            "chitchats_enabled",
            "chitchats_client_id",
            "chitchats_api_key",
            "royal_mail_enabled",
            "royal_mail_api_key"
          ]
        }
      },
      {
        "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.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "company_id",
            "lang_code",
            "company_description",
            "terms",
            "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.10",
            "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": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.35",
            "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",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.45",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "thread_id",
            "object_id",
            "object_type"
          ]
        }
      },
      {
        "table": {
          "table_name": "return_policies",
          "access_type": "ref",
          "possible_keys": [
            "object_type"
          ],
          "key": "object_type",
          "used_key_parts": [
            "object_type",
            "object_id"
          ],
          "key_length": "95",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.80",
            "data_read_per_join": "112"
          },
          "used_columns": [
            "policy_id",
            "object_type",
            "object_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "return_policy_descriptions",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "policy_id",
            "lang_code"
          ],
          "key_length": "9",
          "ref": [
            "cscartdb.return_policies.policy_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.90",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "policy_id",
            "policy",
            "lang_code"
          ]
        }
      },
      {
        "table": {
          "table_name": "shop_policies",
          "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.10",
            "prefix_cost": "1.00",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "company_id",
            "lang_code",
            "shop_policies"
          ]
        }
      }
    ]
  }
}

Result

company_id lang_code shop_policies return_policy 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 pre_moderation pre_moderation_edit pre_moderation_edit_vendors suspend_date grace_period_start last_time_suspended last_debt_notification_time paypal_commerce_platform_account_id discreet_shipping shipstation_enabled shipstation_fulfillment shipstation_api_key shipstation_api_secret shipstation_api_connected shipstation_webhook_id shipstation_store_id shipstation_warehouse_name shipstation_warehouse_street1 shipstation_warehouse_street2 shipstation_warehouse_street3 shipstation_warehouse_country shipstation_warehouse_state shipstation_warehouse_city shipstation_warehouse_postal_code shipstation_warehouse_phone shipstation_warehouse_residential shipstation_warehouse_id shipengine_enabled shipengine_api_key shipengine_api_connected shipengine_webhook_id shipengine_account_id shipengine_external_account_id shipengine_api_key_id discreet_shipping_shop_name vacation_mode vacation_start_date vacation_end_date tagline shopify_token shopify_link_app shopify_api_key shopify_api_secret stripe_connect_account_id stripe_connect_account_type chitchats_enabled chitchats_client_id chitchats_api_key royal_mail_enabled royal_mail_api_key plan seo_name seo_path average_rating company_thread_ids
189 en Item return and exchange are possible for EU countries ONLY. Cancelations are acceptable only 5 days (counting from the ordering time) Please contact the seller if you have any problems with your order. Payment We accept Paypal only. Payment is required within 24 hours of purchase. Shipping: All items will be shipped via Croatian post registered Air mail. If you want some other option, contact me before payment so that I can inform you about potential extra fee. Packages will be shipped within 17 business days after receiving your payment (except for custom orders and items that are complicated to create). You will get a notification as soon as your order has been shipped together with your tracking number. Delivery time can vary according to your location. PLEASE NOTE - The customer is responsible for any fees or customs charges that may be charged by her/his country. Additional Policies and FAQs If you want to place a custom order, feel free to contact me. It may take up to 14 days to fulfill your order. Every product can be made in different sizes and so the price may vary. Item return and exchange are possible for EU countries ONLY. Custom orders can not be returned. Greeting! My name is Ivana and this is my small business fully operated by me only. All products are cut and assembled by hand, using only small hand tools. In my store, you can find products made of real and artificial leather, rubber, and elastic. All products are inspired by black metal and gothic style, so you can combine them for all events, concerts, performances, and anything else you want ;) Kalithea Alternative Mali Lipovec Samobor A Kalithea Alternative Mali Lipovec Samobor Samobor HR 10430 kalitheashop@gmail.com +385958011109 www.etsy.com/shop/KALITHEAA none Y 1718620219 422 a:4:{s:14:"company_fields";a:8:{i:37;s:5:"Ivana";i:38;s:6:"Horvat";i:52;s:0:"";i:58;s:0:"";i:54;s:47:"www.facebook.com/KalitheaAlternativeAccessories";i:55;s:20:"@kalitheaalternative";i:56;s:0:"";i:57;s:0:"";}s:15:"admin_firstname";s:5:"Ivana";s:14:"admin_lastname";s:6:"Horvat";s:6:"fields";a:5:{i:65;s:0:"";i:61;s:47:"www.facebook.com/KalitheaAlternativeAccessories";i:62;s:20:"@kalitheaalternative";i:63;s:0:"";i:64;s:0:"";}} def502001d3b2df11f111c55f8d0c2e44aa6032f0d775a38d5947762138bb0c752d0fd8cb7e90d1f1797af8ca1fbfc2395e2b0808f40dcda1b894c2b60e59925f2f3c93c13ef97aaf6d7e0ea105c6aa857f48c7259f9 1 1 N N N 0 0 0 0 KJW8XGP8N3MEC N N Y N 0 N 0 N N 0 0 0 0 N N Beta Plan kalitheaalternative 189_0