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 = 1943 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  AND cscart_seo_names.company_id = cscart_categories.company_id 
WHERE 
  cscart_categories.category_id = 1943 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.company_id = 1

Query time 0.00055

JSON explain

{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "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": "const",
      "possible_keys": ["PRIMARY", "dispatch"],
      "key": "PRIMARY",
      "key_length": "210",
      "used_key_parts": [
        "object_id",
        "type",
        "dispatch",
        "lang_code",
        "company_id"
      ],
      "ref": ["const", "const", "const", "const", "const"],
      "rows": 1,
      "filtered": 100
    }
  }
}

Result

category_id	parent_id	id_path	level	owner_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	company_id	is_trash	sidebar_r_bg	sidebar_r_title	sidebar_r_desc	sidebar_r_link	sidebar_r_link_text	sidebar_c_bg	sidebar_c_title	sidebar_c_desc	sidebar_c_link	sidebar_c_link_text	commercial_hidden	lang_code	category	description	ldescription	meta_keywords	meta_description	page_title	age_warning_message	name	object_id	type	dispatch	path	seo_name	seo_path
1943	1942	1920/1942/1943	3	0	0	A	47	10	1626840000	N		N	0	N	0			default	0	1	N											0	en	LED Bulbs	<div>Want the benefits of LED lighting without replacing your current fixtures? This array of LED light bulbs offers a fast, simple way to cut down on your building’s energy consumption. Energy-efficient, environmentally conscious, and budget-friendly!</div>			Replacing standard bulbs with LED alternatives is the easiest way to reduce your energy consumption! Go green and save money with LED light bulbs!	LED Bulbs \| Barn Light Electric		led-bulbs	1943	c		1920/1942	led-bulbs	1920/1942