SELECT 
  cscart_categories.category_id, 
  cscart_categories.parent_id, 
  cscart_categories.id_path, 
  cscart_category_descriptions.category, 
  cscart_categories.position, 
  cscart_categories.status, 
  cscart_categories.company_id, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_categories.category_id = cscart_category_descriptions.category_id 
  AND cscart_category_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_categories.category_id 
  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 
  1 
  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.status IN ('A') 
  AND cscart_categories.id_path LIKE '1887/%' 
  AND cscart_categories.company_id = 1 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00085

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_categories.is_trash, cscart_categories.position, cscart_category_descriptions.category",
      "temporary_table": {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "range",
          "possible_keys": ["id_path", "c_status"],
          "key": "id_path",
          "key_length": "767",
          "used_key_parts": ["id_path"],
          "rows": 41,
          "filtered": 100,
          "index_condition": "cscart_categories.id_path like '1887/%'",
          "attached_condition": "cscart_categories.company_id = 1 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.`status` = 'A'"
        },
        "table": {
          "table_name": "cscart_category_descriptions",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["category_id", "lang_code"],
          "ref": ["cscart_migrate.cscart_categories.category_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_category_descriptions.lang_code = 'en')"
        },
        "table": {
          "table_name": "cscart_seo_names",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "dispatch"],
          "key": "PRIMARY",
          "key_length": "210",
          "used_key_parts": [
            "object_id",
            "type",
            "dispatch",
            "lang_code",
            "company_id"
          ],
          "ref": [
            "cscart_migrate.cscart_categories.category_id",
            "const",
            "const",
            "const",
            "const"
          ],
          "rows": 1,
          "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 category position status company_id seo_name seo_path
1889 1888 1887/1888/1889 Cord Hung Pendants 10 A 1 cord-hung-pendant-lighting 1887/1888
1913 1912 1887/1912/1913 Decorative Bulbs 10 A 1 bulbs 1887/1912
1877 1887 1887/1877 Featured Lighting 10 A 1 featured 1887
1897 1896 1887/1896/1897 Gooseneck Lights 10 A 1 gooseneck-lights 1887/1896
1878 1877 1887/1877/1878 NEW Products 10 A 1 new-products 1887/1877
1905 1904 1887/1904/1905 Porch Lights 10 A 1 ceiling-mounted-lights 1887/1904
1910 1909 1887/1909/1910 Table Lamps 10 A 1 table-lamps 1887/1909
1888 1887 1887/1888 Ceiling Lighting 20 A 1 ceiling-lights 1887
1911 1909 1887/1909/1911 Floor Lamps 20 A 1 floor-lamps 1887/1909
1916 1912 1887/1912/1916 Gooseneck Arms 20 A 1 gooseneck-arms 1887/1912
1898 1896 1887/1896/1898 Sign Lights 20 A 1 sign-lights 1887/1896
1890 1888 1887/1888/1890 Stem Mount Pendants 20 A 1 stem-mount-pendant-lighting 1887/1888
1906 1904 1887/1904/1906 Wall Mounted 20 A 1 wall-mounted-lights 1887/1904
1891 1888 1887/1888/1891 Chain Hung Pendant 30 A 1 chain-hung-pendant-lighting 1887/1888
1917 1912 1887/1912/1917 Hardware & Add-Ons 30 A 1 hardware-and-add-ons 1887/1912
1907 1904 1887/1904/1907 Post Mounts 30 A 1 post-mount-lights 1887/1904
1899 1896 1887/1896/1899 Sconces 30 A 1 wall-sconces 1887/1896
1881 1877 1887/1877/1881 Shop by Shade 30 A 1 shop-by-shade 1887/1877
1896 1887 1887/1896 Wall Lighting 30 A 1 wall-lights 1887
1892 1888 1887/1888/1892 Flush Mounts 40 A 1 flush-mount-lights 1887/1888
1918 1912 1887/1912/1918 Glass 40 A 1 glass 1887/1912
1904 1887 1887/1904 Outdoor Lighting 40 A 1 outdoor-lights 1887
1882 1877 1887/1877/1882 Oversized Shades 40 A 1 oversized-shades 1887/1877
1900 1896 1887/1896/1900 Plug-In Sconces 40 A 1 plug-in-wall-sconces 1887/1896
1908 1904 1887/1904/1908 Post Tops 40 A 1 post-top-lights 1887/1904
1909 1887 1887/1909 Lamps 50 A 1 lamps 1887
1893 1888 1887/1888/1893 Multi-Pendant Canopies 50 A 1 multi-pendant-canopies 1887/1888
1883 1877 1887/1877/1883 Schoolhouse 50 A 1 schoolhouse-lighting-collection 1887/1877
1901 1896 1887/1896/1901 Swing Arm Sconces 50 A 1 swing-arm-sconces 1887/1896
2010 1888 1887/1888/2010 Chandelier 51 A 1 chandelier 1887/1888
1894 1888 1887/1888/1894 Chandeliers 60 A 1 chandeliers 1887/1888
1912 1887 1887/1912 Lighting Accessories 60 A 1 accessories 1887
1902 1896 1887/1896/1902 Vanity Lights 60 A 1 vanity-lights 1887/1896
1980 1877 1887/1877/1980 Timber & Ore 61 A 1 timber-ore-lighting-collection 1887/1877
1986 1877 1887/1877/1986 Barn Light Basics 65 A 1 barn-light-basics 1887/1877
1982 1888 1887/1888/1982 Food Warming Pendants 70 A 1 food-warming-lamps 1887/1888