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 = 1878 
  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 = 1878 
  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.00065

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
1878 1877 1887/1877/1878 3 0 0 A 23 10 1626840000 N N 0 N 0 default 0 1 N 0 en NEW Products <span data-contrast="none" xml:lang="EN-US" lang="EN-US" class="TextRun SCXW36686163 BCX0"><span class="NormalTextRun SCXW36686163 BCX0">At Barn Light Electric, we’re constantly dreaming </span><span class="NormalTextRun ContextualSpellingAndGrammarErrorV2 SCXW36686163 BCX0">up</span><span class="NormalTextRun SCXW36686163 BCX0"> brand new lighting creations. With influences ranging from modern and minimalist</span><span class="NormalTextRun SCXW36686163 BCX0"><span> </span>styles to more classic looks, you never know what designs are coming next! As always, our newest fixtures adhere to our strict, hands-on manufacturing principles resulting in rugged, long-lasting lighting.</span></span><span class="EOP SCXW36686163 BCX0" data-ccp-props="{&quot;201341983&quot;:0,&quot;335559739&quot;:160,&quot;335559740&quot;:240}"> </span> vintage lighting, artisan lighting, mid-century lighting, mid century lighting, classic commercial lighting, classic residential lighting, classic home lighting See all the latest lighting fixtures from BLM and transform your space with one of our new designs! Handcrafted, American-made, and customizable! New Lighting Fixtures | Barn Light Electric new-products 1878 c 1887/1877 new-products 1887/1877