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/2920/3741/%' 
  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.00838

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "157.39"
    },
    "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": 108,
            "rows_produced_per_join": 2,
            "filtered": "2.00",
            "index_condition": "(`webmarco`.`cscart_categories`.`id_path` like '2910/2920/3741/%')",
            "cost_info": {
              "read_cost": "151.78",
              "eval_cost": "0.43",
              "prefix_cost": "152.21",
              "data_read_per_join": "12K"
            },
            "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.16",
              "eval_cost": "0.43",
              "prefix_cost": "154.80",
              "data_read_per_join": "8K"
            },
            "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.16",
              "eval_cost": "0.43",
              "prefix_cost": "157.39",
              "data_read_per_join": "3K"
            },
            "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
5313 3741 2910/2920/3741/5313 Kitchen Utensil Sets 0 A 0 0 129 kitchen-utensil-sets 2910/2920/3741
5560 3741 2910/2920/3741/5560 Ricers 0 A 0 0 0 ricers 2910/2920/3741
5627 3741 2910/2920/3741/5627 Scoops 0 A 0 0 2 scoops 2910/2920/3741
6080 3741 2910/2920/3741/6080 Whisks 0 A 0 0 0 whisks 2910/2920/3741
3756 3741 2910/2920/3741/3756 Aprons 0 A 0 0 328 aprons 2910/2920/3741
4505 3741 2910/2920/3741/4505 Baking Peels 0 A 0 0 7 baking-peels 2910/2920/3741
4531 3741 2910/2920/3741/4531 Basters 0 A 0 0 0 basters 2910/2920/3741
4542 3741 2910/2920/3741/4542 Basting Brushes 0 A 0 0 0 basting-brushes 2910/2920/3741
4574 3741 2910/2920/3741/4574 Beverage Dispensers 0 A 0 0 2 beverage-dispensers 2910/2920/3741
4595 3741 2910/2920/3741/4595 Cake Decorating Supplies 0 A 0 0 560 cake-decorating-supplies 2910/2920/3741
4802 3741 2910/2920/3741/4802 Cake Servers 0 A 0 0 8 cake-servers 2910/2920/3741
4789 3741 2910/2920/3741/4789 Can Crushers 0 A 0 0 0 can-crushers 2910/2920/3741
4824 3741 2910/2920/3741/4824 Can Openers 0 A 0 0 0 can-openers 2910/2920/3741
4838 3741 2910/2920/3741/4838 Carving Forks 0 A 0 0 1 carving-forks 2910/2920/3741
4893 3741 2910/2920/3741/4893 Channel Knives 0 A 0 0 0 channel-knives 2910/2920/3741
4925 3741 2910/2920/3741/4925 Colanders & Strainers 0 A 0 0 34 colanders-and-strainers 2910/2920/3741
4959 3741 2910/2920/3741/4959 Condiment Dispensers 0 A 0 0 4 condiment-dispensers 2910/2920/3741
5009 3741 2910/2920/3741/5009 Cookie Cutters 0 A 0 0 122 cookie-cutters 2910/2920/3741
5020 3741 2910/2920/3741/5020 Cookie Presses 0 A 0 0 0 cookie-presses 2910/2920/3741
5028 3741 2910/2920/3741/5028 Cooking Thermometer Accessories 0 A 0 0 6 cooking-thermometer-accessories 2910/2920/3741
5032 3741 2910/2920/3741/5032 Cooking Thermometers 0 A 0 0 18 cooking-thermometers 2910/2920/3741
5040 3741 2910/2920/3741/5040 Cooking Timers 0 A 0 0 0 cooking-timers 2910/2920/3741
5059 3741 2910/2920/3741/5059 Cooking Torches 0 A 0 0 2 cooking-torches 2910/2920/3741
5075 3741 2910/2920/3741/5075 Cooling Racks 0 A 0 0 1 cooling-racks 2910/2920/3741
5114 3741 2910/2920/3741/5114 Cutting Boards 0 A 0 0 18 cutting-boards 2910/2920/3741
4898 3741 2910/2920/3741/4898 Dish Racks & Drain Boards 0 A 0 0 2 dish-racks-and-drain-boards 2910/2920/3741
5033 3741 2910/2920/3741/5033 Dough Wheels 0 A 0 0 0 dough-wheels 2910/2920/3741
5149 3741 2910/2920/3741/5149 Electric Knife Accessories 0 A 0 0 0 electric-knife-accessories 2910/2920/3741
5335 3741 2910/2920/3741/5335 Electric Knives 0 A 0 0 0 electric-knives 2910/2920/3741
5429 3741 2910/2920/3741/5429 Flour Sifters 0 A 0 0 0 flour-sifters 2910/2920/3741
5482 3741 2910/2920/3741/5482 Food & Drink Stencils 0 A 0 0 0 food-and-drink-stencil 2910/2920/3741
5558 3741 2910/2920/3741/5558 Food Crackers 0 A 0 0 1 food-crackers 2910/2920/3741
9524 3741 2910/2920/3741/9524 Food Graters & Zesters 0 A 0 0 8 food-graters-and-zesters-en 2910/2920/3741
5293 3741 2910/2920/3741/5293 Kitchen Shears 0 A 0 0 0 kitchen-shears 2910/2920/3741
5301 3741 2910/2920/3741/5301 Kitchen Slicers 0 A 0 0 0 kitchen-slicers 2910/2920/3741
9329 3741 2910/2920/3741/9329 Kitchen Utensil Sets 0 A 0 0 54 kitchen-utensil-sets-en 2910/2920/3741
5327 3741 2910/2920/3741/5327 Ladles 0 A 0 0 73 ladles 2910/2920/3741
5347 3741 2910/2920/3741/5347 Mashers 0 A 0 0 0 mashers 2910/2920/3741
5352 3741 2910/2920/3741/5352 Measuring Cups & Spoons 0 A 0 0 3 measuring-cups-and-spoons 2910/2920/3741
5369 3741 2910/2920/3741/5369 Meat Tenderizers 0 A 0 0 0 meat-tenderizers 2910/2920/3741
5377 3741 2910/2920/3741/5377 Mixing Bowls 0 A 0 0 0 mixing-bowls 2910/2920/3741
5417 3741 2910/2920/3741/5417 Mortars & Pestles 0 A 0 0 4 mortars-and-pestles 2910/2920/3741
5422 3741 2910/2920/3741/5422 Oil & Vinegar Dispensers 0 A 0 0 11 oil-and-vinegar-dispensers 2910/2920/3741
5428 3741 2910/2920/3741/5428 Oven Bags 0 A 0 0 0 oven-bags 2910/2920/3741
5509 3741 2910/2920/3741/5509 Pasta Molds & Stamps 0 A 0 0 0 pasta-molds-and-stamps 2910/2920/3741
5515 3741 2910/2920/3741/5515 Pastry Blenders 0 A 0 0 0 pastry-blenders 2910/2920/3741
5537 3741 2910/2920/3741/5537 Pastry Cloths 0 A 0 0 1 pastry-cloths 2910/2920/3741
5544 3741 2910/2920/3741/5544 Pizza Cutter Accessories 0 A 0 0 0 pizza-cutter-accessories 2910/2920/3741
5552 3741 2910/2920/3741/5552 Pizza Cutters 0 A 0 0 1 pizza-cutters 2910/2920/3741
9332 3741 2910/2920/3741/9332 Ricers 0 A 0 0 2 ricers-en 2910/2920/3741
5567 3741 2910/2920/3741/5567 Rolling Pin Accessories 0 A 0 0 0 rolling-pin-accessories 2910/2920/3741
5539 3741 2910/2920/3741/5539 Rolling Pins 0 A 0 0 1 rolling-pins 2910/2920/3741
5574 3741 2910/2920/3741/5574 Salad Dressing Mixers & Shakers 0 A 0 0 0 salad-dressing-mixers-and-shakers 2910/2920/3741
5607 3741 2910/2920/3741/5607 Salad Spinners 0 A 0 0 0 salad-spinners 2910/2920/3741
5760 3741 2910/2920/3741/5760 Sink Caddies 0 A 0 0 0 sink-caddies 2910/2920/3741
5783 3741 2910/2920/3741/5783 Sink Mats & Grids 0 A 0 0 0 sink-mats-and-grids 2910/2920/3741
5815 3741 2910/2920/3741/5815 Slotted Spoons 0 A 0 0 71 slotted-spoons 2910/2920/3741
5836 3741 2910/2920/3741/5836 Spatulas 0 A 0 0 71 spatulas 2910/2920/3741
5864 3741 2910/2920/3741/5864 Spice Grinder Accessories 0 A 0 0 0 spice-grinder-accessories 2910/2920/3741
5884 3741 2910/2920/3741/5884 Spice Grinders 0 A 0 0 0 spice-grinders 2910/2920/3741
5910 3741 2910/2920/3741/5910 Spoon Rests 0 A 0 0 0 spoon-rests 2910/2920/3741
5942 3741 2910/2920/3741/5942 Sugar Dispensers 0 A 0 0 0 sugar-dispensers 2910/2920/3741
5982 3741 2910/2920/3741/5982 Sushi Mats 0 A 0 0 9 sushi-mats 2910/2920/3741
6021 3741 2910/2920/3741/6021 Tea Strainers 0 A 0 0 3 tea-strainers 2910/2920/3741
6059 3741 2910/2920/3741/6059 Tongs 0 A 0 0 71 tongs 2910/2920/3741
9528 3741 2910/2920/3741/9528 Whisks 0 A 0 0 3 whisks-en 2910/2920/3741
8718 8706 2910/2920/3741/8706/8718 Bench Scrapers 10 A 0 0 0 bench-scrapers 2910/2920/3741/8706
8333 8304 2910/2920/3741/8304/8333 Can Organizers 10 A 0 0 0 can-organizers 2910/2920/3741/8304
6459 6354 2910/2920/3741/5558/6354/6459 Decorative Nutcrackers 10 A 0 0 0 decorative-nutcrackers 2910/2920/3741/5558/6354
5265 5149 2910/2920/3741/5149/5265 Electric Knife Replacement Blades 10 A 0 0 0 electric-knife-replacement-blades 2910/2920/3741/5149
6579 3741 2910/2920/3741/6579 Food Dispensers 10 A 0 0 1 food-dispensers 2910/2920/3741
5665 5627 2910/2920/3741/5627/5665 Ice Cream Scoops 10 A 0 0 0 ice-cream-scoops 2910/2920/3741/5627
5668 5558 2910/2920/3741/5558/5668 Lobster & Crab Crackers 10 A 0 0 0 lobster-and-crab-crackers 2910/2920/3741/5558
5594 5567 2910/2920/3741/5567/5594 Rolling Pin Covers & Sleeves 10 A 0 0 0 rolling-pin-covers-and-sleeves 2910/2920/3741/5567
6735 3741 2910/2920/3741/6735 Food Graters & Zesters 20 A 0 0 0 food-graters-and-zesters 2910/2920/3741
8726 8706 2910/2920/3741/8706/8726 Bowl Scrapers 20 A 0 0 0 bowl-scrapers 2910/2920/3741/8706
8372 8304 2910/2920/3741/8304/8372 Drinkware Holders 20 A 0 0 1 drinkware-holders 2910/2920/3741/8304
5687 5627 2910/2920/3741/5627/5687 Ice Scoops 20 A 0 0 0 ice-scoops 2910/2920/3741/5627
6354 5558 2910/2920/3741/5558/6354 Nutcrackers 20 A 0 0 1 nutcrackers 2910/2920/3741/5558
5601 5567 2910/2920/3741/5567/5601 Rolling Pin Rings 20 A 0 0 0 rolling-pin-rings 2910/2920/3741/5567
6841 3741 2910/2920/3741/6841 Food Peelers & Corers 30 A 0 0 0 food-peelers-and-corers 2910/2920/3741
8731 8706 2910/2920/3741/8706/8731 Grill Scrapers 30 A 0 0 0 grill-scrapers 2910/2920/3741/8706
8399 8304 2910/2920/3741/8304/8399 Kitchen Cabinet Organizers 30 A 0 0 0 kitchen-cabinet-organizers 2910/2920/3741/8304
5708 5627 2910/2920/3741/5627/5708 Melon Ballers 30 A 0 0 0 melon-ballers 2910/2920/3741/5627
5731 5627 2910/2920/3741/5627/5731 Popcorn & French Fry Scoops 40 A 0 0 0 popcorn-and-french-fry-scoops 2910/2920/3741/5627
6999 3741 2910/2920/3741/6999 Food Steaming Bags 40 A 0 0 18 food-steaming-bags 2910/2920/3741
8430 8304 2910/2920/3741/8304/8430 Kitchen Counter & Beverage Station Organizers 40 A 0 0 0 kitchen-counter-and-beverage-station-organizers 2910/2920/3741/8304
7163 3741 2910/2920/3741/7163 Food Sticks & Skewers 50 A 0 0 17 food-sticks-and-skewers 2910/2920/3741
8509 8304 2910/2920/3741/8304/8509 Kitchen Utensil Holders & Racks 50 A 0 0 19 kitchen-utensil-holders-and-racks 2910/2920/3741/8304
7273 3741 2910/2920/3741/7273 Funnels 60 A 0 0 0 funnels 2910/2920/3741
8528 8304 2910/2920/3741/8304/8528 Knife Blocks & Holders 60 A 0 0 1 knife-blocks-and-holders 2910/2920/3741/8304
7378 3741 2910/2920/3741/7378 Garlic Presses 70 A 0 0 0 garlic-presses 2910/2920/3741
8557 8304 2910/2920/3741/8304/8557 Napkin Holders & Dispensers 70 A 0 0 2 napkin-holders-and-dispensers 2910/2920/3741/8304
7984 3741 2910/2920/3741/7984 Gelatin Molds 80 A 0 0 5 gelatin-molds 2910/2920/3741
8574 8304 2910/2920/3741/8304/8574 Paper Towel Holders & Dispensers 80 A 0 0 0 paper-towel-holders-and-dispensers 2910/2920/3741/8304
8038 3741 2910/2920/3741/8038 Ice Cube Trays 90 A 0 0 0 ice-cube-trays 2910/2920/3741
8605 8304 2910/2920/3741/8304/8605 Pot Racks 90 A 0 0 0 pot-racks 2910/2920/3741/8304
8097 3741 2910/2920/3741/8097 Jerky Guns 100 A 0 0 0 jerky-guns 2910/2920/3741
8628 8304 2910/2920/3741/8304/8628 Spice Organizers 100 A 0 0 2 spice-organizers 2910/2920/3741/8304
8137 3741 2910/2920/3741/8137 Kitchen Knives 110 A 0 0 42 kitchen-knives 2910/2920/3741
8642 8304 2910/2920/3741/8304/8642 Straw Holders & Dispensers 110 A 0 0 0 straw-holders-and-dispensers 2910/2920/3741/8304
8223 3741 2910/2920/3741/8223 Kitchen Molds 120 A 0 0 562 kitchen-molds 2910/2920/3741
8654 8304 2910/2920/3741/8304/8654 Sugar Caddies 120 A 0 0 0 sugar-caddies 2910/2920/3741/8304
8304 3741 2910/2920/3741/8304 Kitchen Organizers 130 A 0 0 9 kitchen-organizers 2910/2920/3741
8670 8304 2910/2920/3741/8304/8670 Toothpick Holders & Dispensers 130 A 0 0 0 toothpick-holders-and-dispensers 2910/2920/3741/8304
8706 3741 2910/2920/3741/8706 Kitchen Scrapers 140 A 0 0 0 kitchen-scrapers 2910/2920/3741
8684 8304 2910/2920/3741/8304/8684 Utensil & Flatware Trays 140 A 0 0 0 utensil-and-flatware-trays 2910/2920/3741/8304
9024 3741 2910/2920/3741/9024 Oven Mitts & Pot Holders 150 A 0 0 5 oven-mitts-and-pot-holders-en 2910/2920/3741