SELECT 
  categories.category_id, 
  categories.parent_id, 
  categories.id_path, 
  category_descriptions.category, 
  categories.position, 
  categories.status, 
  categories.company_id, 
  categories.storefront_id, 
  seo_names.name as seo_name, 
  seo_names.path as seo_path, 
  category_descriptions.mega_m_category_banner_url, 
  category_descriptions.mega_m_category_svg_icon 
FROM 
  categories 
  LEFT JOIN category_descriptions ON categories.category_id = category_descriptions.category_id 
  AND category_descriptions.lang_code = 'en' 
  LEFT JOIN seo_names ON seo_names.object_id = categories.category_id 
  AND seo_names.type = 'c' 
  AND seo_names.dispatch = '' 
  AND seo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, categories.usergroup_ids) 
    OR FIND_IN_SET(1, categories.usergroup_ids)
  ) 
  AND categories.status IN ('A') 
  AND categories.storefront_id IN (0, 1) 
  AND categories.deleted_at IS NULL 
  AND categories.category_id IN(
    2, 15, 23, 36, 4, 45, 46, 71, 75, 81, 100, 
    109, 118, 17, 152, 156, 183, 205, 2566, 
    2843, 5906, 5907, 5908, 5909, 5910, 
    5913, 5938, 5941, 5942, 5943, 5945, 
    5946, 5947, 6524, 5948, 5950, 5952, 
    5951, 5953, 5954, 5955, 5956, 5957, 
    5958, 5960, 5969, 5972, 6001, 6004, 
    6006, 6007, 6011, 6017, 6018, 6020, 
    6033, 6141, 6472, 6473, 6525, 6528, 
    6530, 6531, 5937, 6539, 6540, 6624, 
    51, 6667, 8960, 11379, 11489, 11490, 
    12570, 12571, 14879, 15564, 15565, 
    18669, 18670, 18671, 18672
  ) 
  AND categories.company_id = 521 
ORDER BY 
  categories.is_trash asc, 
  categories.position asc, 
  category_descriptions.category asc

Query time 0.00986

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "37.06"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id",
              "index_categories_on_deleted_at"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 82,
            "rows_produced_per_join": 0,
            "filtered": "0.06",
            "cost_info": {
              "read_cost": "36.98",
              "eval_cost": "0.01",
              "prefix_cost": "36.99",
              "data_read_per_join": "134"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "deleted_at",
              "storefront_id"
            ],
            "attached_condition": "((`goimagine`.`categories`.`company_id` = 521) and ((`goimagine`.`categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`goimagine`.`categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`goimagine`.`categories`.`usergroup_ids`))) and (`goimagine`.`categories`.`status` = 'A') and (`goimagine`.`categories`.`storefront_id` in (0,1)) and (`goimagine`.`categories`.`deleted_at` is null) and (`goimagine`.`categories`.`category_id` in (2,15,23,36,4,45,46,71,75,81,100,109,118,17,152,156,183,205,2566,2843,5906,5907,5908,5909,5910,5913,5938,5941,5942,5943,5945,5946,5947,6524,5948,5950,5952,5951,5953,5954,5955,5956,5957,5958,5960,5969,5972,6001,6004,6006,6007,6011,6017,6018,6020,6033,6141,6472,6473,6525,6528,6530,6531,5937,6539,6540,6624,51,6667,8960,11379,11489,11490,12570,12571,14879,15564,15565,18669,18670,18671,18672)))"
          }
        },
        {
          "table": {
            "table_name": "category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "goimagine.categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.02",
              "eval_cost": "0.01",
              "prefix_cost": "37.01",
              "data_read_per_join": "232"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category",
              "mega_m_category_banner_url",
              "mega_m_category_svg_icon"
            ]
          }
        },
        {
          "table": {
            "table_name": "seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "goimagine.categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.04",
              "eval_cost": "0.01",
              "prefix_cost": "37.06",
              "data_read_per_join": "86"
            },
            "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 seo_name seo_path mega_m_category_banner_url mega_m_category_svg_icon
6524 2843 2843/6524 Animals 0 A 521 0 animals 2843
12571 11489 11489/12571 Betsey Johnson 0 A 521 0 betsey-johnson 11489
12570 2843 2843/12570 Betsey Johnson 0 A 521 0 betsey-johnson 2843
5958 2843 2843/5958 Blingy 0 A 521 0 blingy-minders 2843
6624 0 6624 Bundles 0 A 521 0 bundles-en
5913 2843 2843/5913 Butterflies 0 A 521 0 butterfly-minders 2843
15565 5952 2843/6524/5952/15565 Cats 0 A 521 0 cats 2843/6524/5952
18672 18670 18670/18672 Christmas 0 A 521 0 christmas 18670
6530 5960 2843/5960/6530 Christmas 0 A 521 0 christmas 2843/5960
5908 5906 5906/5908 Comfort Grip Pens 0 A 521 0 comfort-grip-pens 5906
5909 5906 5906/5909 Cover Your Wax Pens 0 A 521 0 protect-your-wax-pens 5906
5947 2843 2843/5947 Crawls, Flies, & Jumps (Insects, Bugs, Lizards, Frogs, etc.) 0 A 521 0 bugs-beetles-insect-minders 2843
6540 5937 5937/6540 Cross Stitch 0 A 521 0 cross-stitch 5937
6539 5937 5937/6539 Diamond Painting 0 A 521 0 diamond-painting 5937
5906 0 5906 Diamond Painting Pens 0 A 521 0 diamond-painting-pens
5937 0 5937 DMC Color Charts, DP Journal 0 A 521 0 dmc-color-charts
5951 5952 2843/6524/5952/5951 Dogs 0 A 521 0 dog-minders 2843/6524/5952
5942 2843 2843/5942 Dragonflies 0 A 521 0 dragonfly-minders 2843
5907 5906 5906/5907 Ergonomic Pens 0 A 521 0 ergonomic-pens 5906
6001 2843 2843/6001 Everything Else 0 A 521 0 everything-else-minders 2843
6528 6473 2843/6473/6528 Fall 0 A 521 0 fall 2843/6473
5954 6524 2843/6524/5954 Farm Animals 0 A 521 0 farm-animal-minders 2843/6524
5955 2843 2843/5955 Fashion 0 A 521 0 fashion-minders 2843
5948 6524 2843/6524/5948 Feathers and Wings 0 A 521 0 birds-minders 2843/6524
5957 2843 2843/5957 Flowers 0 A 521 0 flower-minders 2843
14879 2843 2843/14879 Food/Drinks 0 A 521 0 food-drinks 2843
6531 5960 2843/5960/6531 Halloween 0 A 521 0 halloween 2843/5960
5960 2843 2843/5960 Holidays 0 A 521 0 holiday-minders 2843
5950 2843 2843/5950 In the Water 0 A 521 0 river-ocean-and-sea-minders 2843
5953 6524 2843/6524/5953 In the Wild 0 A 521 0 forest-animal-minders 2843/6524
18671 18670 18670/18671 LSU 0 A 521 0 lsu 18670
5938 0 5938 Magnetic Minder Display - Storage Frames 0 A 521 0 magnetic-minder-storage-display-frames
5946 2843 2843/5946 Medical & First Responder 0 A 521 0 medical-and-first-responder-minders 2843
8960 2843 2843/8960 Metal 0 A 521 0 metal-minders 2843
15564 2843 2843/15564 Music 0 A 521 0 music 2843
6472 2843 2843/6472 Mythical 0 A 521 0 mythical 2843
6006 2843 2843/6006 Native American, Indian, Southwestern 0 A 521 0 native-american-indian-southwestern 2843
6141 2843 2843/6141 Nautical 0 A 521 0 nautical 2843
2843 0 2843 Needle & Cover Minders 0 A 521 0 cross-stitch
18670 0 18670 Ornaments 0 A 521 0 ornaments-en-71
6004 6524 2843/6524/6004 Other Animals 0 A 521 0 other-animal-minders 2843/6524
18669 0 18669 Oyster Shell Salt and Pepper Set 0 A 521 0 oyster-shell-ornaments
5945 2843 2843/5945 Patriotic 0 A 521 0 patriotic-minders 2843
5952 6524 2843/6524/5952 Pets 0 A 521 0 cat-minders 2843/6524
11490 0 11490 Pinless Brooch 0 A 521 0 brooch-en
11489 0 11489 Refrigerator Magnet 0 A 521 0 refrigerator-magnet-en
5969 2843 2843/5969 Resin 0 A 521 0 resin-minders 2843
5941 0 5941 Scissor Fobs 0 A 521 0 scissor-fobs
6473 2843 2843/6473 Seasons 0 A 521 0 seasons 2843
5910 5906 5906/5910 Short and Stout Pens 0 A 521 0 short-and-stout-pens 5906
5956 2843 2843/5956 Skulls 0 A 521 0 skull-minders 2843
6007 5906 5906/6007 Sparkly Pens 0 A 521 0 sparkly-pens 5906
6011 2843 2843/6011 Spiritual/Religious 0 A 521 0 spiritual-religious 2843
11379 2843 2843/11379 Sports 0 A 521 0 sports 2843
5943 2843 2843/5943 Steampunk 0 A 521 0 steampunk-minders 2843
6033 2843 2843/6033 Teacher 0 A 521 0 teacher 2843
6018 5906 5906/6018 Unique Shaped Pens 0 A 521 0 unique-shaped-pens 5906
5972 2843 2843/5972 Vintage 0 A 521 0 vintage-minders 2843
6020 2843 2843/6020 Wax Holder, Trash Drill Holder, Spare Thread Holder 0 A 521 0 wax-holder-trash-drill-holder-spare-thread-holder-minders 2843
6525 6473 2843/6473/6525 Winter 0 A 521 0 winter 2843/6473
6017 6524 2843/6524/6017 Zoo Animals 0 A 521 0 zoo-animals 2843/6524