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 = 99 
  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 = 99

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
99 en Order Cancellations will only be accepted within 3 days of your order placement date, we need to purchase materials and stock to manufacture your order and will begin cutting patterns so we will not be able to cancel an order after this time frame. Once you receive your order, returns for any reason must be made within 30 days of receiving the item. You must contact us within 14 days to discuss any issues with your item before you can initiate any returns, please do not send an item back without talking with us first about your issue. Returns must not be worn or used any more than an initial try on for size. We however cannot accept a return on a hygienic related items such as gags. We do not offer returns for change of mind. The items sold here are custom made and sized personally to each specific customer on a for 'commission' basis so refunds would only be considered for major defects or problems with the item. If for some reason, you are not happy with your order upon arrival please contact us to discuss. In appropriate circumstances we will consider giving a refund of the purchase price (not including shipping and fees) upon return of the item if it is original condition, we will always defer to replacing the item as a first solution before moving to a refund as an option. In the event of any defect other than what would be considered normal for a handmade item, liability is limited to the replacement or repair of the goods supplied or payment of the amount to replace or repair the goods, at the discretion of Restricted Senses For items lost in transit please contact us and we will replace the item, generally all items of value will be shipped with registered tracking post and will require a signature upon delivery to minimise loss. Welcome to Restricted Senses! Established in 2014, we have been fortunate enough to produce and ship tens of thousands of hoods, gags and armbinders around the world to happy customers to enjoy and spice up their naughty time. Here you will find Fetish products manufactured to a high standard of quality. Our products are hand made on the Central Coast, Australia. Restricted Senses 58 Bateay Bay Rd Bateau Bay A Restricted Senses 58 Bateay Bay Rd Bateau Bay NSW AU 2261 contact@restrictedsenses.com +61414060935 https://www.etsy.com/shop/RestrictedSenses none Y 1713429922 207 a:4:{s:14:"company_fields";a:8:{i:37;s:0:"";i:38;s:0:"";i:52;s:29:"https://restrictedsenses.com/";i:58;s:0:"";i:54;s:0:"";i:55;s:0:"";i:56;s:0:"";i:57;s:0:"";}s:15:"admin_firstname";s:0:"";s:14:"admin_lastname";s:0:"";s:6:"fields";a:5:{i:65;s:0:"";i:61;s:0:"";i:62;s:0:"";i:63;s:0:"";i:64;s:0:"";}} def502001d4e94beb30638e9b815e643f6e044fa188c8661577d079e95415ee8cc007a8e1149a38fad4c3c0c2d467c35cd4a21cf5491c07f13c3a89ca115f105cfb3cf4d7ef00341d054f6dc9222e28aa024b93eb3e4 1 1 N N N 0 0 0 0 235L54VP7UV3E Y N Y N 0 N 0 N N 0 0 0 0 High Quality Fetish Starts Here... N N Beta Plan restrictedsenses 99_0