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_categories.storefront_id, 
  cscart_categories.product_count, 
  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' 
WHERE 
  1 = 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 '6473/7822/%' 
  AND cscart_categories.storefront_id IN (0, 1) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00259

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "83.55"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "c_status",
              "id_path"
            ],
            "key": "id_path",
            "used_key_parts": [
              "id_path"
            ],
            "key_length": "767",
            "rows_examined_per_scan": 57,
            "rows_produced_per_join": 1,
            "filtered": "2.00",
            "index_condition": "(`webmarco`.`cscart_categories`.`id_path` like '6473/7822/%')",
            "cost_info": {
              "read_cost": "80.58",
              "eval_cost": "0.23",
              "prefix_cost": "80.81",
              "data_read_per_join": "6K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "usergroup_ids",
              "status",
              "product_count",
              "position",
              "is_trash",
              "storefront_id"
            ],
            "attached_condition": "(((`webmarco`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`webmarco`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`webmarco`.`cscart_categories`.`usergroup_ids`)) and (`webmarco`.`cscart_categories`.`status` = 'A') and (`webmarco`.`cscart_categories`.`storefront_id` in (0,1)))"
          }
        },
        {
          "table": {
            "table_name": "cscart_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "webmarco.cscart_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.14",
              "eval_cost": "0.23",
              "prefix_cost": "82.18",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "webmarco.cscart_categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.14",
              "eval_cost": "0.23",
              "prefix_cost": "83.55",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id parent_id id_path category position status company_id storefront_id product_count seo_name seo_path
7918 7822 6473/7822/7918 Brackets & Reinforcement Braces 10 A 0 0 0 brackets-and-reinforcement-braces-en 6473/7822
8001 7921 6473/7822/7921/8001 Bungee Cords 10 A 0 0 0 bungee-cords-en 6473/7822/7921
7971 7919 6473/7822/7919/7971 Cabinet & Furniture Keyhole Escutcheons 10 A 0 0 0 cabinet-and-furniture-keyhole-escutcheons-en 6473/7822/7919
8060 7933 6473/7822/7933/8060 Chain Connectors & Links 10 A 0 0 0 chain-connectors-and-links 6473/7822/7933
8023 7931 6473/7822/7931/8023 Drywall Anchors 10 A 0 0 0 drywall-anchors 6473/7822/7931
8078 7941 6473/7822/7941/8078 Garden Hose Storage 10 A 0 0 0 garden-hose-storage-en 6473/7822/7941
7972 7919 6473/7822/7919/7972 Cabinet Backplates 20 A 0 0 0 cabinet-backplates-en 6473/7822/7919
7919 7822 6473/7822/7919 Cabinet Hardware 20 A 0 0 7 cabinet-hardware-en 6473/7822
8002 7921 6473/7822/7921/8002 Chains 20 A 0 0 0 chains-en 6473/7822/7921
8061 7933 6473/7822/7933/8061 Gear Ties 20 A 0 0 0 gear-ties 6473/7822/7933
8024 7931 6473/7822/7931/8024 Nails 20 A 0 0 7 nails 6473/7822/7931
8079 7941 6473/7822/7941/8079 Tool & Equipment Belts 20 A 0 0 0 tool-and-equipment-belts-en 6473/7822/7941
7973 7919 6473/7822/7919/7973 Cabinet Catches 30 A 0 0 0 cabinet-catches-en 6473/7822/7919
7920 7822 6473/7822/7920 Casters 30 A 0 0 0 casters-en 6473/7822
8062 7933 6473/7822/7933/8062 Lifting Hooks, Clamps & Shackles 30 A 0 0 0 lifting-hooks-clamps-and-shackles 6473/7822/7933
8025 7931 6473/7822/7931/8025 Nuts & Bolts 30 A 0 0 0 nuts-and-bolts 6473/7822/7931
8003 7921 6473/7822/7921/8003 Pull Chains 30 A 0 0 0 pull-chains-en 6473/7822/7921
8080 7941 6473/7822/7941/8080 Tool Bags 30 A 0 0 1 tool-bags-en 6473/7822/7941
7974 7919 6473/7822/7919/7974 Cabinet Doors 40 A 0 0 0 cabinet-doors-en 6473/7822/7919
7921 7822 6473/7822/7921 Chain, Wire & Rope 40 A 0 0 0 chain-wire-and-rope-en 6473/7822
8026 7931 6473/7822/7931/8026 Rivets 40 A 0 0 0 rivets 6473/7822/7931
8004 7921 6473/7822/7921/8004 Ropes & Hardware Cable 40 A 0 0 0 ropes-and-hardware-cable-en 6473/7822/7921
8081 7941 6473/7822/7941/8081 Tool Boxes 40 A 0 0 0 tool-boxes-en 6473/7822/7941
8063 7933 6473/7822/7933/8063 Utility Buckles 40 A 0 0 0 utility-buckles 6473/7822/7933
7975 7919 6473/7822/7919/7975 Cabinet Knobs & Handles 50 A 0 0 0 cabinet-knobs-and-handles-en 6473/7822/7919
7922 7822 6473/7822/7922 Coils 50 A 0 0 0 coils-en 6473/7822
8027 7931 6473/7822/7931/8027 Screw Posts 50 A 0 0 0 screw-posts 6473/7822/7931
8005 7921 6473/7822/7921/8005 Tie Down Straps 50 A 0 0 0 tie-down-straps-en 6473/7822/7921
8082 7941 6473/7822/7941/8082 Tool Cabinets & Chests 50 A 0 0 0 tool-cabinets-and-chests-en 6473/7822/7941
7923 7822 6473/7822/7923 Concrete Molds 60 A 0 0 0 concrete-molds-en 6473/7822
8028 7931 6473/7822/7931/8028 Screws 60 A 0 0 117 screws 6473/7822/7931
8083 7941 6473/7822/7941/8083 Tool Organizer Liners & Inserts 60 A 0 0 0 tool-organizer-liners-and-inserts-en 6473/7822/7941
8006 7921 6473/7822/7921/8006 Twine 60 A 0 0 0 twine-en 6473/7822/7921
7924 7822 6473/7822/7924 Dowel Pins & Rods 70 A 0 0 0 dowel-pins-and-rods-en 6473/7822
8029 7931 6473/7822/7931/8029 Threaded Rods 70 A 0 0 0 threaded-rods 6473/7822/7931
8084 7941 6473/7822/7941/8084 Tool Sheaths 70 A 0 0 0 tool-sheaths-en 6473/7822/7941
8007 7921 6473/7822/7921/8007 Utility Wire 70 A 0 0 0 utility-wire-en 6473/7822/7921
7925 7822 6473/7822/7925 Drawer Slides 80 A 0 0 0 drawer-slides-en 6473/7822
8030 7931 6473/7822/7931/8030 Washers 80 A 0 0 4 washers 6473/7822/7931
8085 7941 6473/7822/7941/8085 Work Benches 80 A 0 0 0 work-benches-en 6473/7822/7941
7926 7822 6473/7822/7926 Drop Cloths 90 A 0 0 0 drop-cloths-en 6473/7822
7927 7822 6473/7822/7927 Filters & Screens 100 A 0 0 0 filters-and-screens-en 6473/7822
7928 7822 6473/7822/7928 Flagging & Caution Tape 110 A 0 0 0 flagging-and-caution-tape-en 6473/7822
7929 7822 6473/7822/7929 Gas Hoses 120 A 0 0 0 gas-hoses-en 6473/7822
7930 7822 6473/7822/7930 Ground Spikes 130 A 0 0 0 ground-spikes-en 6473/7822
7931 7822 6473/7822/7931 Hardware Fasteners 140 A 0 0 135 hardware-fasteners-en 6473/7822
7932 7822 6473/7822/7932 Hinges 150 A 0 0 0 hinges-en 6473/7822
7933 7822 6473/7822/7933 Hooks, Buckles & Fasteners 160 A 0 0 0 hooks-buckles-and-fasteners-en 6473/7822
7934 7822 6473/7822/7934 Lubrication Hoses 170 A 0 0 0 lubrication-hoses-en 6473/7822
7935 7822 6473/7822/7935 Metal Casting Molds 180 A 0 0 0 metal-casting-molds-en 6473/7822
7936 7822 6473/7822/7936 Moving & Soundproofing Blankets & Covers 190 A 0 0 0 moving-and-soundproofing-blankets-and-covers-en 6473/7822
7937 7822 6473/7822/7937 Pneumatic Hoses 200 A 0 0 8 pneumatic-hoses-en 6473/7822
7938 7822 6473/7822/7938 Post Base Plates 210 A 0 0 0 post-base-plates-en 6473/7822
7939 7822 6473/7822/7939 Springs 220 A 0 0 0 springs 6473/7822
7940 7822 6473/7822/7940 Tarps 230 A 0 0 0 tarps-en 6473/7822
7941 7822 6473/7822/7941 Tool Storage & Organization 240 A 0 0 0 tool-storage-and-organization-en 6473/7822
7942 7822 6473/7822/7942 Wall Jacks & Braces 250 A 0 0 0 wall-jacks-and-braces-en 6473/7822