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 '4001/%' 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND cscart_categories.category_id IN(
    266, 
    356, 
    393, 
    1516, 
    1936, 
    2601, 
    2653, 
    2760, 
    2851, 
    2910, 
    2912, 
    2917, 
    2985, 
    2944, 
    3002, 
    3048, 
    3046, 
    3060, 
    2916, 
    3087, 
    3080, 
    3085, 
    3095, 
    3144, 
    3185, 
    3105, 
    3224, 
    3228, 
    3231, 
    3390, 
    3407, 
    3487, 
    3532, 
    3591, 
    3631, 
    3535, 
    3617, 
    3933, 
    4001, 
    4254, 
    4003, 
    4525, 
    4693, 
    5264, 
    4299, 
    4271, 
    4313, 
    4317, 
    4257, 
    4418, 
    4321, 
    4564, 
    4575, 
    4551, 
    4746, 
    4334, 
    4903, 
    4787, 
    4973, 
    5014, 
    5030, 
    5039, 
    3648, 
    3685, 
    5152, 
    4805, 
    5098, 
    5164, 
    4933, 
    5256, 
    5270, 
    5372, 
    5495, 
    5506, 
    5511, 
    5519, 
    5522, 
    4808, 
    5597, 
    5742, 
    5821, 
    4431, 
    5968, 
    5457, 
    6343, 
    6426, 
    6288, 
    6448, 
    2920, 
    3741, 
    6579, 
    6473, 
    6809, 
    6870, 
    6897, 
    6928, 
    6975, 
    5362, 
    6980, 
    7008, 
    5847, 
    7022, 
    7093, 
    7217, 
    7135, 
    7242, 
    7301, 
    7454, 
    7464, 
    7797, 
    7849, 
    7080, 
    7435, 
    8229, 
    5684, 
    6536, 
    8356, 
    8362, 
    8440, 
    5577, 
    8784, 
    8817, 
    8823, 
    6328, 
    6340, 
    6471, 
    9052, 
    9053, 
    9289, 
    2954, 
    9386, 
    4910, 
    9434, 
    9435, 
    4701, 
    9662, 
    9697, 
    9720, 
    9997, 
    10041, 
    10260, 
    10269, 
    10300, 
    10265, 
    10334, 
    10335, 
    10336, 
    10437, 
    10438
  ) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00388

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "209.67"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "id_path",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 149,
            "rows_produced_per_join": 0,
            "filtered": "0.11",
            "cost_info": {
              "read_cost": "209.23",
              "eval_cost": "0.03",
              "prefix_cost": "209.26",
              "data_read_per_join": "1K"
            },
            "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`.`id_path` like '4001/%') and (`webmarco`.`cscart_categories`.`storefront_id` in (0,1)) and (`webmarco`.`cscart_categories`.`category_id` in (266,356,393,1516,1936,2601,2653,2760,2851,2910,2912,2917,2985,2944,3002,3048,3046,3060,2916,3087,3080,3085,3095,3144,3185,3105,3224,3228,3231,3390,3407,3487,3532,3591,3631,3535,3617,3933,4001,4254,4003,4525,4693,5264,4299,4271,4313,4317,4257,4418,4321,4564,4575,4551,4746,4334,4903,4787,4973,5014,5030,5039,3648,3685,5152,4805,5098,5164,4933,5256,5270,5372,5495,5506,5511,5519,5522,4808,5597,5742,5821,4431,5968,5457,6343,6426,6288,6448,2920,3741,6579,6473,6809,6870,6897,6928,6975,5362,6980,7008,5847,7022,7093,7217,7135,7242,7301,7454,7464,7797,7849,7080,7435,8229,5684,6536,8356,8362,8440,5577,8784,8817,8823,6328,6340,6471,9052,9053,9289,2954,9386,4910,9434,9435,4701,9662,9697,9720,9997,10041,10260,10269,10300,10265,10334,10335,10336,10437,10438)))"
          }
        },
        {
          "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.17",
              "eval_cost": "0.03",
              "prefix_cost": "209.47",
              "data_read_per_join": "708"
            },
            "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.17",
              "eval_cost": "0.03",
              "prefix_cost": "209.67",
              "data_read_per_join": "294"
            },
            "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
4808 4254 4001/4254/4808 Biometric Monitor Accessories 0 A 0 0 0 biometric-monitor-accessories 4001/4254
4431 4254 4001/4254/4431 Biometric Monitors 0 A 0 0 4 biometric-monitors 4001/4254
4525 4003 4001/4003/4525 Cosmetics 0 A 0 0 70 cosmetics 4001/4003
4334 4003 4001/4003/4334 Hair Care 0 A 0 0 283 hair-care 4001/4003
4257 4003 4001/4003/4257 Shaving & Grooming 0 A 0 0 406 shaving-and-grooming 4001/4003
4551 4525 4001/4003/4525/4551 Bath & Body 10 A 0 0 299 bath-and-body 4001/4003/4525
4299 5264 4001/4003/4525/4693/5264/4299 Nail Dryers 10 A 0 0 12 nail-dryers 4001/4003/4525/4693/5264
4003 4001 4001/4003 Personal Care 10 A 0 0 224 personal-care 4001
4254 4001 4001/4254 Health Care 20 A 0 0 77 health-care 4001
5264 4693 4001/4003/4525/4693/5264 Nail Tools 30 A 0 0 87 nail-tools 4001/4003/4525/4693
4746 4551 4001/4003/4525/4551/4746 Bath Brushes 40 A 0 0 1 bath-brushes 4001/4003/4525/4551
5597 4808 4001/4254/4808/5597 Body Weight Scale Accessories 40 A 0 0 1 body-weight-scale-accessories 4001/4254/4808
5256 4933 4001/4003/4334/4933/5256 Hair Dryers 40 A 0 0 86 hair-dryers 4001/4003/4334/4933
4418 4257 4001/4003/4257/4418 Hair Clipper & Trimmer Accessories 50 A 0 0 11 hair-clipper-and-trimmer-accessories 4001/4003/4257
5270 4933 4001/4003/4334/4933/5270 Hair Straighteners 50 A 0 0 7 hair-straighteners 4001/4003/4334/4933
4693 4525 4001/4003/4525/4693 Cosmetic Tools 80 A 0 0 68 cosmetic-tools 4001/4003/4525
4903 4334 4001/4003/4334/4903 Hair Styling Tool Accessories 110 A 0 0 2 hair-styling-tool-accessories 4001/4003/4334
5968 4431 4001/4254/4431/5968 Body Weight Scales 120 A 0 0 16 body-weight-scales 4001/4254/4431
4933 4334 4001/4003/4334/4933 Hair Styling Tools 120 A 0 0 79 hair-styling-tools 4001/4003/4334