SELECT 
  *, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_category_descriptions.category_id = cscart_categories.category_id 
  AND cscart_category_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 1626 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
WHERE 
  cscart_categories.category_id = 1626 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  )

Query time 0.00031

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00183779,
    "const_condition": "1",
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "cscart_category_descriptions",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["category_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"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00183779,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_seo_names.`type` = 'c' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
        }
      }
    ]
  }
}

Result

category_id parent_id id_path level company_id storefront_id usergroup_ids status product_count position timestamp is_op localization age_verification age_limit parent_age_verification parent_age_limit selected_views default_view product_details_view product_columns is_trash is_default category_type ab__fn_category_status ab__fn_label_color ab__fn_label_background ab__fn_use_origin_image ab__lc_catalog_image_control ab__lc_landing ab__lc_subsubcategories ab__lc_menu_id ab__lc_how_to_use_menu ab__lc_inherit_control ec_wc_cat wc_cat_id default_view_desktop default_view_tablet default_view_mobile nt_cscart_app_level lang_code category description meta_keywords meta_description page_title age_warning_message mega_m_category_banner_url mega_m_category_svg_icon ab__fn_label_text ab__fn_label_show name object_id type dispatch path seo_name seo_path
1626 1617 1617/1626 2 0 0 0 A 0 90 1744754400 N N 0 N 0 default 0 N N C Y #ffffff #333333 N none N 0 0 N N N 0 0 en Socks socks, comfortable socks, fashion socks, men’s socks, women’s socks, cotton socks, wool socks, ankle socks, knee-high socks, patterned socks, dress socks Shop a wide variety of socks, from everyday essentials to fashion-forward designs. Find socks that offer comfort, style, and durability, perfect for work, play, or lounging. Socks | Comfortable and Stylish Socks for Every Occasion Y socks 1626 c 1617 socks 1617