SELECT 
  company_descriptions.*, 
  companies.*, 
  cscart_vendor_plan_descriptions.plan, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
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 = 102 
  AND cscart_seo_names.type = 'm' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
WHERE 
  companies.company_id = 102

Query time 0.00032

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "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.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "11K"
          },
          "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",
            "paypal_for_marketplaces_account_id",
            "paypal_email",
            "paypal_base_for_commission",
            "paypal_verification",
            "ppa_first_name",
            "ppa_last_name",
            "paypal_commerce_platform_account_id",
            "suspend_date",
            "grace_period_start",
            "last_time_suspended",
            "last_debt_notification_time",
            "tax_number",
            "saved_shippings_state",
            "stripe_connect_account_id",
            "stripe_connect_account_type",
            "registered_from_storefront_id",
            "shipstation_username",
            "shipstation_password"
          ]
        }
      },
      {
        "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"
          ]
        }
      }
    ]
  }
}

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 pre_moderation pre_moderation_edit pre_moderation_edit_vendors plan_id paypal_for_marketplaces_account_id paypal_email paypal_base_for_commission paypal_verification ppa_first_name ppa_last_name paypal_commerce_platform_account_id suspend_date grace_period_start last_time_suspended last_debt_notification_time tax_number saved_shippings_state stripe_connect_account_id stripe_connect_account_type registered_from_storefront_id shipstation_username shipstation_password plan seo_name seo_path
102 en <p>We believe the throwaway culture of modern consumerism is not just wasteful, but also the root cause of some of today’s most challenging problems. Products often are not made to last and are not treasured due to the use of cheap materials, a lack of craftsmanship and a disconnect between maker and consumer. Mass production may have its place for some goods, but not ours. We carry only goods that are made individually or in small batches— handcrafted by skilled and passionate makers who have developed their talents through hours, days and years of honest dedication to their craft. </p> Sophia's Shop 17 8th Ave New York A Sophia's Shop 17 8th Ave New York NY 10014 sophiashop221@gmail.com 917-229-0099 none Y 1727387925 18,13 0 N N N 4 not_checked 0 0 0 0 acct_1SAX0GRr4ccBhBbf E Ambassadors sophias-shop