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 = 15001 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
WHERE 
  cscart_categories.category_id = 15001 
  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.00007

JSON explain

{
  "query_block": {
    "select_id": 1,
    "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"],
          "rows": 2,
          "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 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 category_code xml_external_category_id storefront_id default_view_desktop default_view_tablet default_view_mobile lang_code category description meta_keywords meta_description page_title age_warning_message ab__fn_label_text ab__fn_label_show name object_id type dispatch path seo_name seo_path
15001 28 28/15001 2 0 0 A 0 120 1614084759 N N 0 N 0 default 0 N N C Y #ffffff #333333 N none N 0 0 N N 15001 1 en Cat Supplies <p>Human and cat are both happy. A cat purrs within a range of 20-140 Hz which is known to be medically therapeutic for illnesses in humans. A cat's purr can not only lower stress it can also help labored breathing, lower blood pressure, help heal infections, and even heal bones. Owning any pet is good for your heart. Cats in particular lower your stress level—possibly since they don’t require as much effort as dogs—and lower the amount of anxiety in your life. Petting a cat has a positive calming effect. One study found that over a 10-year period cat owners were 30 percent less likely to die of a heart attack or stroke than non-cat owners (although this might just be because cat owners are more relaxed and have lower stress in general).</p> Cat Supplies, Pet Supplies Find a great variety of Cat supplies at ebonza.eu. Cat apparel, cat beds, cat food, cat furniture, cat boxes, cat litters. Cat Supplies | Cat Apparel | eBonza.eu Y pet-supplies-cat-supplies 15001 c 28 pet-supplies-cat-supplies 28