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/%' 
  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.00330

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "210.36"
    },
    "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.31",
            "cost_info": {
              "read_cost": "209.17",
              "eval_cost": "0.09",
              "prefix_cost": "209.26",
              "data_read_per_join": "2K"
            },
            "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 '2910/%') 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.46",
              "eval_cost": "0.09",
              "prefix_cost": "209.81",
              "data_read_per_join": "1K"
            },
            "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.46",
              "eval_cost": "0.09",
              "prefix_cost": "210.36",
              "data_read_per_join": "792"
            },
            "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
3087 2916 2910/2916/3087 Clocks 0 A 0 0 3 clocks 2910/2916
3390 3228 2910/3105/3228/3390 Clothing & Closet Storage 0 A 0 0 5 clothing-and-closet-storage-en 2910/3105/3228
2916 2910 2910/2916 Decor 0 A 0 0 5036 decor 2910
2944 2910 2910/2944 Fireplace & Wood Stove Accessories 0 A 0 0 79 fireplace-and-wood-stove-accessories 2910
3080 2910 2910/3080 Household Appliance Accessories 0 A 0 0 1038 household-appliance-accessories 2910
2912 2910 2910/2912 Household Appliances 0 A 0 0 2447 household-appliances 2910
3105 2910 2910/3105 Household Supplies 0 A 0 0 1950 household-supplies 2910
2920 2910 2910/2920 Kitchen & Dining 0 A 0 0 2755 kitchen-and-dining 2910
3741 2920 2910/2920/3741 Kitchen Tools & Utensils 0 A 0 0 412 kitchen-tools-and-utensils 2910/2920
3224 3105 2910/3105/3224 Moisture Absorbers 0 A 0 0 1 moisture-absorbers 2910/3105
3407 3390 2910/3105/3228/3390/3407 Shoe Racks & Organizers 0 A 0 0 13 shoe-racks-and-organizers-en 2910/3105/3228/3390
3228 3105 2910/3105/3228 Storage & Organization 0 A 0 0 119 storage-and-organization 2910/3105
3231 3105 2910/3105/3231 Waste Containment Accessories 0 A 0 0 1 waste-containment-accessories 2910/3105
3002 2944 2910/2944/3002 Wood Stove Fans & Blowers 0 A 0 0 1 wood-stove-fans-and-blowers 2910/2944
3085 3080 2910/3080/3085 Air Conditioner Accessories 10 A 0 0 236 air-conditioner-accessories 2910/3080
2917 2912 2910/2912/2917 Climate Control Appliances 10 A 0 0 286 climate-control-appliances 2910/2912
6579 3741 2910/2920/3741/6579 Food Dispensers 10 A 0 0 1 food-dispensers 2910/2920/3741
3095 3085 2910/3080/3085/3095 Air Conditioner Filters 20 A 0 0 1 air-conditioner-filters 2910/3080/3085
5522 2910 2910/5522 Lighting Accessories 20 A 0 0 33 lighting-accessories 2910
3144 3080 2910/3080/3144 Fan Accessories 40 A 0 0 1 fan-accessories 2910/3080
2954 2917 2910/2912/2917/2954 Fans 60 A 0 0 0 fans 2910/2912/2917
3060 3046 2910/2912/3046/3060 Washing Machines 60 A 0 0 6 washing-machines-en 2910/2912/3046
3046 2912 2910/2912/3046 Laundry Appliances 80 A 0 0 0 laundry-appliances-en 2910/2912
3185 3080 2910/3080/3185 Humidifier Accessories 80 A 0 0 2 humidifier-accessories 2910/3080
2985 2917 2910/2912/2917/2985 Humidifiers 90 A 0 0 1 humidifiers 2910/2912/2917
3048 2912 2910/2912/3048 Vacuums 100 A 0 0 4 vacuums-en 2910/2912
3487 2916 2910/2916/3487 Piggy Banks & Money Jars 710 A 0 0 42 piggy-banks-and-money-jars 2910/2916