Sandbox

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

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
15002	28	28/15002	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	15002		1				en	Dog Supplies	<p>Benefits of having a dog</p>Dog owners generally have a stronger immune system, helping them to stay well and spend less time getting over sickness and bugs. In fact, dog owners visit the doctor less often than non-dog owners, and are less likely to be on medication.<br><ol><li>People recover faster from illnesses and even have higher survival rates after a heart attack if they have a dog.</li><li>Children who have a furry friend have fewer days off school due to sickness.</li><li>Children with chronic illnesses often cope better with treatment when they have a canine companion</li><li>Dogs provide us with a sense of emotional wellbeing thanks to the unconditional love they give, which is why they are also great company and comfort for people who live alone</li><li>Owning a dog can help people to recover from personal trauma, such as bereavement</li><li>Dog ownership can help those with mental health problems to recover and feel better.</li></ol><p>On top of all these serious benefits of dogs, they are also fantastic fun. They always make you laugh, they're there at the end of a hard day, and they'll almost always be on hand for a reassuring hug when you need it!</p>	Dog Supplies, Pet Supplies	Shop various dog supplies at ebonza.eu. Dog apparel, dog beds, dog food, dog houses, dog diapers and dog treats.	Dog Supplies \| Dog Treats \| eBonza.eu			Y	pet-supplies-dog-supplies	15002	c		28	pet-supplies-dog-supplies	28