Sandbox

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

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "209.77"
    },
    "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.14",
            "cost_info": {
              "read_cost": "209.22",
              "eval_cost": "0.04",
              "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 '5372/%') 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.21",
              "eval_cost": "0.04",
              "prefix_cost": "209.52",
              "data_read_per_join": "888"
            },
            "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.21",
              "eval_cost": "0.04",
              "prefix_cost": "209.77",
              "data_read_per_join": "369"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id	parent_id	id_path	category	position	status	product_count	seo_name	seo_path
5495	5372	5372/5495	Hobbies & Creative Arts	0	A	86	hobbies-and-creative-arts-en	5372
5506	5495	5372/5495/5506	Collectibles	10	A	6	collectibles	5372/5495
7093	7022	5372/5495/5847/7022/7093	Guitar Accessories	10	A	0	guitar-accessories	5372/5495/5847/7022
5511	5506	5372/5495/5506/5511	Sports Collectibles	10	A	0	sports-collectibles	5372/5495/5506
5519	5511	5372/5495/5506/5511/5519	Sports Fan Accessories	10	A	1	sports-fan-accessories	5372/5495/5506/5511
5577	5495	5372/5495/5577	Arts & Crafts	30	A	6	arts-and-crafts	5372/5495
8784	5577	5372/5495/5577/8784	Art & Crafting Tools	40	A	7	art-and-crafting-tools	5372/5495/5577
8823	8817	5372/5495/5577/8784/8817/8823	Sewing Machines	40	A	1	sewing-machines	5372/5495/5577/8784/8817
7217	7093	5372/5495/5847/7022/7093/7217	Guitar Humidifiers	60	A	1	guitar-humidifiers	5372/5495/5847/7022/7093
5847	5495	5372/5495/5847	Musical Instrument & Orchestra Accessories	100	A	0	musical-instrument-and-orchestra-accessories-en	5372/5495
8817	8784	5372/5495/5577/8784/8817	Textile Craft Machines	100	A	0	textile-craft-machines	5372/5495/5577/8784
7022	5847	5372/5495/5847/7022	String Instrument Accessories	130	A	0	string-instrument-accessories	5372/5495/5847