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 '2910/2916/%' 
  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.00541

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "192.15"
    },
    "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": 132,
            "rows_produced_per_join": 2,
            "filtered": "2.00",
            "index_condition": "(`webmarco`.`cscart_categories`.`id_path` like '2910/2916/%')",
            "cost_info": {
              "read_cost": "185.28",
              "eval_cost": "0.53",
              "prefix_cost": "185.81",
              "data_read_per_join": "15K"
            },
            "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": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.64",
              "eval_cost": "0.53",
              "prefix_cost": "188.98",
              "data_read_per_join": "10K"
            },
            "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": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.64",
              "eval_cost": "0.53",
              "prefix_cost": "192.15",
              "data_read_per_join": "4K"
            },
            "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
3064 2916 2910/2916/3064 Cardboard Cutouts 0 A 0 0 0 cardboard-cutouts 2910/2916
3201 2916 2910/2916/3201 Dried Flowers 0 A 0 0 0 dried-flowers 2910/2916
2926 2916 2910/2916/2926 Address Signs 0 A 0 0 0 address-signs 2910/2916
2931 2916 2910/2916/2931 Artificial Flora 0 A 0 0 0 artificial-flora 2910/2916
2940 2916 2910/2916/2940 Artificial Food 0 A 0 0 0 artificial-food 2910/2916
2951 2916 2910/2916/2951 Artwork 0 A 0 0 23 artwork 2910/2916
2978 2916 2910/2916/2978 Backrest Pillows 0 A 0 0 13 backrest-pillows 2910/2916
2983 2916 2910/2916/2983 Baskets 0 A 0 0 12 baskets 2910/2916
2989 2916 2910/2916/2989 Bird & Wildlife Feeder Accessories 0 A 0 0 0 bird-and-wildlife-feeder-accessories 2910/2916
2993 2916 2910/2916/2993 Bird & Wildlife Feeders 0 A 0 0 0 bird-and-wildlife-feeders 2910/2916
3014 2916 2910/2916/3014 Bird & Wildlife House Accessories 0 A 0 0 0 bird-and-wildlife-house-accessories 2910/2916
3017 2916 2910/2916/3017 Bird & Wildlife Houses 0 A 0 0 0 bird-and-wildlife-houses 2910/2916
3040 2916 2910/2916/3040 Bird Baths 0 A 0 0 0 bird-baths 2910/2916
3052 2916 2910/2916/3052 Bookends 0 A 0 0 0 bookends 2910/2916
3073 2916 2910/2916/3073 Chair & Sofa Cushions 0 A 0 0 33 chair-and-sofa-cushions 2910/2916
3079 2916 2910/2916/3079 Clock Parts 0 A 0 0 0 clock-parts 2910/2916
3087 2916 2910/2916/3087 Clocks 0 A 0 0 3 clocks 2910/2916
3116 2916 2910/2916/3116 Coat & Hat Racks 0 A 0 0 0 coat-and-hat-racks 2910/2916
3140 2916 2910/2916/3140 Decorative Bells 0 A 0 0 0 decorative-bells 2910/2916
3145 2916 2910/2916/3145 Decorative Bottles 0 A 0 0 9 decorative-bottles 2910/2916
3149 2916 2910/2916/3149 Decorative Bowls 0 A 0 0 20 decorative-bowls 2910/2916
3153 2916 2910/2916/3153 Decorative Jars 0 A 0 0 5 decorative-jars 2910/2916
3159 2916 2910/2916/3159 Decorative Plaques 0 A 0 0 7 decorative-plaques 2910/2916
3181 2916 2910/2916/3181 Decorative Plates 0 A 0 0 95 decorative-plates 2910/2916
3187 2916 2910/2916/3187 Decorative Trays 0 A 0 0 10 decorative-trays 2910/2916
3192 2916 2910/2916/3192 Door Mats 0 A 0 0 53 door-mats 2910/2916
3197 2916 2910/2916/3197 Dreamcatchers 0 A 0 0 0 dreamcatchers 2910/2916
3206 2916 2910/2916/3206 Ecospheres 0 A 0 0 0 ecospheres 2910/2916
3212 2916 2910/2916/3212 Figurines 0 A 0 0 3 figurines 2910/2916
3217 2916 2910/2916/3217 Finials 0 A 0 0 0 finials 2910/2916
3234 2916 2910/2916/3234 Flag & Windsock Accessories 0 A 0 0 0 flag-and-windsock-accessories 2910/2916
3253 2916 2910/2916/3253 Flags & Windsocks 0 A 0 0 3 flags-and-windsocks 2910/2916
9595 2916 2910/2916/9595 Mailboxe 0 A 0 0 1 mailboxe 2910/2916
3096 2916 2910/2916/3096 Seasonal & Holiday Decorations 0 A 0 0 246 seasonal-and-holiday-decorations 2910/2916
3221 2916 2910/2916/3221 Shadow Boxes 0 A 0 0 0 shadow-boxes 2910/2916
3237 2916 2910/2916/3237 Slipcovers 0 A 0 0 0 slipcovers 2910/2916
3243 2916 2910/2916/3243 Snow Globes 0 A 0 0 10 snow-globes 2910/2916
3250 2916 2910/2916/3250 Suncatchers 0 A 0 0 0 suncatchers 2910/2916
2961 2951 2910/2916/2951/2961 Decorative Tapestries 10 A 0 0 1 decorative-tapestries 2910/2916/2951
3427 3425 2910/2916/3425/3427 Mailbox Covers 10 A 0 0 0 mailbox-covers 2910/2916/3425
3108 3096 2910/2916/3096/3108 Advent Calendars 10 A 0 0 1 advent-calendars 2910/2916/3096
3340 3335 2910/2916/3335/3340 Air Fresheners 10 A 0 0 10 air-fresheners 2910/2916/3335
3092 3087 2910/2916/3087/3092 Alarm Clocks 10 A 0 0 15 alarm-clocks 2910/2916/3087
3022 3017 2910/2916/3017/3022 Bat Houses 10 A 0 0 0 bat-houses 2910/2916/3017
2998 2993 2910/2916/2993/2998 Bird Feeders 10 A 0 0 0 bird-feeders 2910/2916/2993
3321 3317 2910/2916/3317/3321 Candle & Oil Warmers 10 A 0 0 18 candle-and-oil-warmers 2910/2916/3317
3353 3343 2910/2916/3343/3353 Curtain & Drape Rings 10 A 0 0 8 curtain-and-drape-rings 2910/2916/3343
3424 3414 2910/2916/3414/3424 Curtains & Drapes 10 A 0 0 9 curtains-and-drapes 2910/2916/3414
3239 3234 2910/2916/3234/3239 Flag & Windsock Pole Lights 10 A 0 0 0 flag-and-windsock-pole-lights 2910/2916/3234
3271 3264 2910/2916/3264/3271 Fountain & Pond Accessories 10 A 0 0 0 fountain-and-pond-accessories 2910/2916/3264
3431 3425 2910/2916/3425/3431 Mailbox Enclosures 20 A 0 0 0 mailbox-enclosures 2910/2916/3425
3028 3017 2910/2916/3017/3028 Birdhouses 20 A 0 0 1 birdhouses 2910/2916/3017
3004 2993 2910/2916/2993/3004 Butterfly Feeders 20 A 0 0 0 butterfly-feeders 2910/2916/2993
3326 3317 2910/2916/3317/3326 Candle Holders 20 A 0 0 2 candle-holders 2910/2916/3317
3344 3335 2910/2916/3335/3344 Candles 20 A 0 0 356 candles 2910/2916/3335
3118 3096 2910/2916/3096/3118 Christmas Tree Skirts 20 A 0 0 0 christmas-tree-skirts 2910/2916/3096
3359 3343 2910/2916/3343/3359 Curtain & Drape Rods 20 A 0 0 0 curtain-and-drape-rods 2910/2916/3343
3093 3087 2910/2916/3087/3093 Desk & Shelf Clocks 20 A 0 0 0 desk-and-shelf-clocks 2910/2916/3087
3244 3234 2910/2916/3234/3244 Flag & Windsock Pole Mounting Hardware & Kits 20 A 0 0 0 flag-and-windsock-pole-mounting-hardware-and-kits 2910/2916/3234
3275 3264 2910/2916/3264/3275 Fountains & Waterfalls 20 A 0 0 9 fountains-and-waterfalls 2910/2916/3264
2967 2951 2910/2916/2951/2967 Posters, Prints, & Visual Artwork 20 A 0 0 2 posters-prints-and-visual-artwork 2910/2916/2951
3433 3414 2910/2916/3414/3433 Stained Glass Panels 20 A 0 0 0 stained-glass-panels 2910/2916/3414
3034 3017 2910/2916/3017/3034 Butterfly Houses 30 A 0 0 0 butterfly-houses 2910/2916/3017
3327 3317 2910/2916/3317/3327 Candle Snuffers 30 A 0 0 0 candle-snuffers 2910/2916/3317
3143 3096 2910/2916/3096/3143 Christmas Tree Stands 30 A 0 0 39 christmas-tree-stands 2910/2916/3096
3366 3343 2910/2916/3343/3366 Curtain Holdbacks & Tassels 30 A 0 0 0 curtain-holdbacks-and-tassels 2910/2916/3343
3248 3234 2910/2916/3234/3248 Flag & Windsock Poles 30 A 0 0 0 flag-and-windsock-poles 2910/2916/3234
3099 3087 2910/2916/3087/3099 Floor & Grandfather Clocks 30 A 0 0 0 floor-and-grandfather-clocks 2910/2916/3087
3352 3335 2910/2916/3335/3352 Fragrance Oil 30 A 0 0 24 fragrance-oil 2910/2916/3335
3439 3425 2910/2916/3425/3439 Mailbox Flags 30 A 0 0 0 mailbox-flags 2910/2916/3425
3281 3264 2910/2916/3264/3281 Ponds 30 A 0 0 0 ponds 2910/2916/3264
2972 2951 2910/2916/2951/2972 Sculptures & Statues 30 A 0 0 5 sculptures-and-statues 2910/2916/2951
3010 2993 2910/2916/2993/3010 Squirrel Feeders 30 A 0 0 0 squirrel-feeders 2910/2916/2993
3440 3414 2910/2916/3414/3440 Window Blinds & Shades 30 A 0 0 0 window-blinds-and-shades 2910/2916/3414
3445 3425 2910/2916/3425/3445 Mailbox Posts 40 A 0 0 0 mailbox-posts 2910/2916/3425
3103 3087 2910/2916/3087/3103 Wall Clocks 40 A 0 0 32 wall-clocks 2910/2916/3087
3146 3096 2910/2916/3096/3146 Easter Egg Decorating Kits 40 A 0 0 68 easter-egg-decorating-kits 2910/2916/3096
3357 3335 2910/2916/3335/3357 Incense 40 A 0 0 73 incense 2910/2916/3335
3331 3317 2910/2916/3317/3331 Incense Holders 40 A 0 0 0 incense-holders 2910/2916/3317
3447 3414 2910/2916/3414/3447 Window Films 40 A 0 0 36 window-films 2910/2916/3414
3384 3343 2910/2916/3343/3384 Window Treatment Replacement Parts 40 A 0 0 0 window-treatment-replacement-parts 2910/2916/3343
3154 3096 2910/2916/3096/3154 Holiday Ornament Displays & Stands 50 A 0 0 33 holiday-ornament-displays-and-stands 2910/2916/3096
3448 3425 2910/2916/3425/3448 Mailbox Replacement Doors 50 A 0 0 0 mailbox-replacement-doors 2910/2916/3425
3361 3335 2910/2916/3335/3361 Potpourri 50 A 0 0 0 potpourri 2910/2916/3335
3452 3414 2910/2916/3414/3452 Window Screens 50 A 0 0 0 window-screens 2910/2916/3414
3160 3096 2910/2916/3096/3160 Holiday Ornament Hooks 60 A 0 0 0 holiday-ornament-hooks 2910/2916/3096
3379 3335 2910/2916/3335/3379 Wax Tarts 60 A 0 0 0 wax-tarts 2910/2916/3335
3456 3414 2910/2916/3414/3456 Window Valances & Cornices 60 A 0 0 0 window-valances-and-cornices 2910/2916/3414
3182 3096 2910/2916/3096/3182 Holiday Ornaments 70 A 0 0 280 holiday-ornaments 2910/2916/3096
3189 3096 2910/2916/3096/3189 Holiday Stocking Hangers 80 A 0 0 0 holiday-stocking-hangers 2910/2916/3096
3196 3096 2910/2916/3096/3196 Holiday Stockings 90 A 0 0 1 holiday-stockings 2910/2916/3096
3204 3096 2910/2916/3096/3204 Japanese Traditional Dolls 100 A 0 0 0 japanese-traditional-dolls 2910/2916/3096
3208 3096 2910/2916/3096/3208 Nativity Sets 110 A 0 0 0 nativity-sets 2910/2916/3096
3215 3096 2910/2916/3096/3215 Seasonal Village Sets & Accessories 120 A 0 0 15 seasonal-village-sets-and-accessories 2910/2916/3096
3256 2916 2910/2916/3256 Sundials 380 A 0 0 0 sundials 2910/2916
3259 2916 2910/2916/3259 Flameless Candles 390 A 0 0 0 flameless-candles 2910/2916
3264 2916 2910/2916/3264 Fountains & Ponds 400 A 0 0 14 fountains-and-ponds 2910/2916
3270 2916 2910/2916/3270 Throw Pillows 410 A 0 0 8 throw-pillows 2910/2916
3276 2916 2910/2916/3276 Trunks 420 A 0 0 0 trunks 2910/2916
3282 2916 2910/2916/3282 Vase Fillers & Table Scatters 430 A 0 0 0 vase-fillers-and-table-scatters 2910/2916
3306 2916 2910/2916/3306 Garden & Stepping Stones 440 A 0 0 0 garden-and-stepping-stones 2910/2916
3308 2916 2910/2916/3308 Vases 450 A 0 0 71 vases 2910/2916
3309 2916 2910/2916/3309 Growth Charts 460 A 0 0 0 growth-charts 2910/2916
3312 2916 2910/2916/3312 Home Decor Decals 470 A 0 0 0 home-decor-decals 2910/2916
3314 2916 2910/2916/3314 Wallpaper 480 A 0 0 2 wallpaper 2910/2916
3317 2916 2910/2916/3317 Home Fragrance Accessories 490 A 0 0 0 home-fragrance-accessories 2910/2916
3318 2916 2910/2916/3318 Weather Vanes & Roof Decor 500 A 0 0 0 weather-vanes-and-roof-decor 2910/2916
3323 2916 2910/2916/3323 Wind Chimes 510 A 0 0 0 wind-chimes 2910/2916
3334 2916 2910/2916/3334 Wind Wheels & Spinners 520 A 0 0 0 wind-wheels-and-spinners 2910/2916
3335 2916 2910/2916/3335 Home Fragrances 530 A 0 0 153 home-fragrances 2910/2916
3338 2916 2910/2916/3338 Window Magnets 540 A 0 0 0 window-magnets 2910/2916
3343 2916 2910/2916/3343 Window Treatment Accessories 550 A 0 0 0 window-treatment-accessories 2910/2916
3386 2916 2910/2916/3386 Hourglasses 560 A 0 0 0 hourglasses 2910/2916
3401 2916 2910/2916/3401 House Numbers & Letters 570 A 0 0 0 house-numbers-and-letters 2910/2916
3412 2916 2910/2916/3412 Lawn Ornaments & Garden Sculptures 580 A 0 0 0 lawn-ornaments-and-garden-sculptures 2910/2916
3414 2916 2910/2916/3414 Window Treatments 590 A 0 0 0 window-treatments 2910/2916
3423 2916 2910/2916/3423 Mail Slots 600 A 0 0 0 mail-slots 2910/2916
3425 2916 2910/2916/3425 Mailbox Accessories 610 A 0 0 0 mailbox-accessories 2910/2916
3453 2916 2910/2916/3453 Mailboxes 620 A 0 0 36 mailboxes 2910/2916
3454 2916 2910/2916/3454 Mirrors 630 A 0 0 29 mirrors 2910/2916
3459 2916 2910/2916/3459 Music Boxes 640 A 0 0 0 music-boxes 2910/2916
3461 2916 2910/2916/3461 World Globes 650 A 0 0 5 world-globes 2910/2916
3462 2916 2910/2916/3462 Napkin Rings 660 A 0 0 0 napkin-rings 2910/2916
3468 2916 2910/2916/3468 Novelty Signs 670 A 0 0 0 novelty-signs 2910/2916
3469 2916 2910/2916/3469 Wreaths & Garlands 680 A 0 0 0 wreaths-and-garlands 2910/2916
3472 2916 2910/2916/3472 Ottoman Cushions 690 A 0 0 0 ottoman-cushions 2910/2916
3478 2916 2910/2916/3478 Picture Frames 700 A 0 0 81 picture-frames 2910/2916
3487 2916 2910/2916/3487 Piggy Banks & Money Jars 710 A 0 0 42 piggy-banks-and-money-jars 2910/2916
3488 2916 2910/2916/3488 Rain Gauges 720 A 0 0 0 rain-gauges 2910/2916
3491 2916 2910/2916/3491 Rain Chains 730 A 0 0 0 rain-chains 2910/2916
3498 2916 2910/2916/3498 Refrigerator Magnets 740 A 0 0 13 refrigerator-magnets 2910/2916
3507 2916 2910/2916/3507 Rugs 750 A 0 0 13 rugs 2910/2916