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_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.level <= 3 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND cscart_categories.category_id IN(
    266, 1936, 2601, 2910, 2912, 2917, 2928, 
    3648, 3751, 3796, 2653, 2658, 9276, 
    9997, 9999, 10000, 9998, 10004, 10006, 
    10008, 10009, 10010, 10011, 10012, 
    10013, 10014, 10015, 10016, 10017, 
    10018, 10019, 10022, 10023, 10025, 
    10026, 10027, 10028, 10029, 10030, 
    10031, 10032, 10034, 10036, 10037, 
    10041, 10077, 10166, 10167, 10168, 
    10374, 10375, 10376, 10377, 10378, 
    10381, 10382, 10611, 10383, 10384, 
    10385, 10387, 10388, 10389, 10390, 
    10392, 10394, 10395, 10450, 10451, 
    10452, 10514, 10513, 10515, 10516, 
    10576, 10577, 10578, 10579, 10583, 
    10587, 10588, 10589, 10590, 10591, 
    10592, 10593, 10612, 10594, 10595, 
    10596, 10597, 10602, 10598, 10599, 
    10600, 10601, 10603, 10604, 10605, 
    10606, 10607, 10610
  ) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00189

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "144.89"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 102,
            "rows_produced_per_join": 0,
            "filtered": "0.67",
            "cost_info": {
              "read_cost": "143.12",
              "eval_cost": "0.14",
              "prefix_cost": "143.26",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "level",
              "company_id",
              "usergroup_ids",
              "status",
              "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`.`level` <= 3) and (`webmarco`.`cscart_categories`.`storefront_id` in (0,1)) and (`webmarco`.`cscart_categories`.`category_id` in (266,1936,2601,2910,2912,2917,2928,3648,3751,3796,2653,2658,9276,9997,9999,10000,9998,10004,10006,10008,10009,10010,10011,10012,10013,10014,10015,10016,10017,10018,10019,10022,10023,10025,10026,10027,10028,10029,10030,10031,10032,10034,10036,10037,10041,10077,10166,10167,10168,10374,10375,10376,10377,10378,10381,10382,10611,10383,10384,10385,10387,10388,10389,10390,10392,10394,10395,10450,10451,10452,10514,10513,10515,10516,10576,10577,10578,10579,10583,10587,10588,10589,10590,10591,10592,10593,10612,10594,10595,10596,10597,10602,10598,10599,10600,10601,10603,10604,10605,10606,10607,10610)))"
          }
        },
        {
          "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": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.68",
              "eval_cost": "0.14",
              "prefix_cost": "144.07",
              "data_read_per_join": "2K"
            },
            "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": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.68",
              "eval_cost": "0.14",
              "prefix_cost": "144.89",
              "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 seo_name seo_path
9276 2658 2653/2658/9276 Accessories 0 A 0 0 accessories 2653/2658
2658 2653 2653/2658 Clothing 0 A 0 0 clothing-en 2653
2912 2910 2910/2912 Household Appliances 0 A 0 0 household-appliances 2910
266 0 266 Services 0 A 0 0 services-en
2653 0 2653 Apparel & Accessories 10 A 0 0 apparel-and-accessories
2917 2912 2910/2912/2917 Climate Control Appliances 10 A 0 0 climate-control-appliances 2910/2912
3796 3751 3648/3751/3796 Activity Tables 20 A 0 0 activity-tables 3648/3751
2910 0 2910 Home & Garden 40 A 0 0 home-and-garden
3648 0 3648 Furniture 50 A 0 0 furniture
1936 266 266/1936 Economy & Finance Services 60 A 0 0 economy-and-finance-services 266
2601 1936 266/1936/2601 Payments and Telephone Top-ups 110 A 0 0 payments-and-telephone-top-ups-en 266/1936
3751 3648 3648/3751 Tables 1290 A 0 0 tables 3648