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_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.parent_id IN (0) 
  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.00392

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "178.95"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "parent",
              "p_category_id"
            ],
            "key": "parent",
            "used_key_parts": [
              "parent_id",
              "category_id"
            ],
            "key_length": "6",
            "rows_examined_per_scan": 149,
            "rows_produced_per_join": 0,
            "filtered": "2.00",
            "index_condition": "((`webmarco`.`cscart_categories`.`parent_id` = 0) 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)))",
            "cost_info": {
              "read_cost": "149.00",
              "eval_cost": "0.01",
              "prefix_cost": "178.80",
              "data_read_per_join": "385"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "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`.`storefront_id` in (0,1)))"
          }
        },
        {
          "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.06",
              "eval_cost": "0.01",
              "prefix_cost": "178.88",
              "data_read_per_join": "265"
            },
            "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.06",
              "eval_cost": "0.01",
              "prefix_cost": "178.95",
              "data_read_per_join": "110"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id	id_path	category	position	status	seo_name
266	266	Services	0	A	services-en
2653	2653	Apparel & Accessories	10	A	apparel-and-accessories
4001	4001	Health & Beauty	20	A	health-and-beauty
5362	5362	Baby & Toddler	25	A	baby-and-toddler
3532	3532	Food, Beverages & Tobacco	30	A	food-beverages-and-tobacco
2910	2910	Home & Garden	40	A	home-and-garden
3648	3648	Furniture	50	A	furniture
5372	5372	Arts & Entertainment	60	A	arts-and-entertainment-en
5457	5457	Office Supplies	60	A	office-supplies-en
5684	5684	Sporting Goods	80	A	sporting-goods
6288	6288	Toys & Games	90	A	toys-and-games
4271	4271	Electronics	110	A	electronics-en
6328	6328	Vehicles & Parts	120	A	vehicles-and-parts
6473	6473	Hardware	130	A	hardware
5742	5742	Cameras & Optics	140	A	cameras-and-optics
9697	9697	All Categories	190	A	all-categories-en