SELECT 
  jmj_categories.category_id, 
  jmj_categories.parent_id, 
  jmj_categories.id_path, 
  jmj_category_descriptions.category, 
  jmj_categories.position, 
  jmj_categories.status, 
  jmj_categories.company_id, 
  jmj_categories.storefront_id, 
  jmj_categories.ab__lc_catalog_image_control 
FROM 
  jmj_categories 
  LEFT JOIN jmj_category_descriptions ON jmj_categories.category_id = jmj_category_descriptions.category_id 
  AND jmj_category_descriptions.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    jmj_categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, jmj_categories.usergroup_ids) 
    OR FIND_IN_SET(1, jmj_categories.usergroup_ids)
  ) 
  AND jmj_categories.status IN ('A') 
  AND jmj_categories.category_id != 1 
  AND jmj_categories.parent_id != 1 
  AND jmj_categories.level <= 3 
  AND jmj_categories.storefront_id IN (0, 1) 
  AND jmj_categories.category_id IN(
    3, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 
    19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
    29, 30, 2, 36, 31, 32, 33, 34, 35, 37, 38, 
    39, 40, 41, 42, 43, 44, 45, 47, 48, 49, 
    50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 
    46, 60, 61, 62, 63, 64, 65, 66, 4, 67, 69, 
    70, 71, 73, 74, 75, 76, 77, 68, 83, 84, 
    85, 86, 78, 79, 88, 5, 89, 92, 93, 94, 95, 
    96, 90, 97, 98, 99, 91, 100, 101, 102, 
    103, 82, 104, 105, 106, 107, 108, 80, 
    109, 110, 111, 112, 113, 114, 115, 81, 
    116, 117, 118, 119, 120, 121, 122
  ) 
ORDER BY 
  jmj_categories.is_trash asc, 
  jmj_categories.position asc, 
  jmj_category_descriptions.category asc

Query time 0.00724

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "30.62"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "jmj_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "parent",
              "p_category_id"
            ],
            "rows_examined_per_scan": 120,
            "rows_produced_per_join": 0,
            "filtered": "0.83",
            "cost_info": {
              "read_cost": "29.22",
              "eval_cost": "0.20",
              "prefix_cost": "29.42",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "level",
              "company_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control",
              "storefront_id"
            ],
            "attached_condition": "(((`jmj_test`.`jmj_categories`.`usergroup_ids` = '') or find_in_set(0,`jmj_test`.`jmj_categories`.`usergroup_ids`) or find_in_set(1,`jmj_test`.`jmj_categories`.`usergroup_ids`)) and (`jmj_test`.`jmj_categories`.`status` = 'A') and (`jmj_test`.`jmj_categories`.`category_id` <> 1) and (`jmj_test`.`jmj_categories`.`parent_id` <> 1) and (`jmj_test`.`jmj_categories`.`level` <= 3) and (`jmj_test`.`jmj_categories`.`storefront_id` in (0,1)) and (`jmj_test`.`jmj_categories`.`category_id` in (3,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,2,36,31,32,33,34,35,37,38,39,40,41,42,43,44,45,47,48,49,50,51,52,53,54,55,56,57,58,59,46,60,61,62,63,64,65,66,4,67,69,70,71,73,74,75,76,77,68,83,84,85,86,78,79,88,5,89,92,93,94,95,96,90,97,98,99,91,100,101,102,103,82,104,105,106,107,108,80,109,110,111,112,113,114,115,81,116,117,118,119,120,121,122)))"
          }
        },
        {
          "table": {
            "table_name": "jmj_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "jmj_test.jmj_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "0.20",
              "prefix_cost": "30.62",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id parent_id id_path category position status company_id storefront_id ab__lc_catalog_image_control
100 91 5/91/100 Bath Towels 10 A 0 0 none
89 5 5/89 Bed Linen & Furnishing 10 A 0 0 none
92 89 5/89/92 Bedsheets 10 A 0 0 none
97 90 5/90/97 Carpets 10 A 0 0 none
36 2 2/36 Ethnic Wear 10 A 0 0 none
109 80 78/80/109 Jeans 10 A 0 0 none
60 46 2/46/60 Jeans 10 A 0 0 none
83 68 4/68/83 Jeans 10 A 0 0 none
20 19 3/19/20 Jeans 10 A 0 0 none
26 25 3/25/26 Kurta 10 A 0 0 none
31 36 2/36/31 Kurta 10 A 0 0 none
104 82 4/82/104 Kurta 10 A 0 0 none
88 79 78/79/88 kurta 10 A 0 0 none
116 81 78/81/116 Kurta 10 A 0 0 none
106 82 4/82/106 Sherwanis 10 A 0 0 none
69 67 4/67/69 T-Shirts 10 A 0 0 none
9 8 3/8/9 T-Shirts 10 A 0 0 none
67 4 4/67 Top Wear 10 A 0 0 none
79 78 78/79 Top Wear 10 A 0 0 none
8 3 3/8 Top Wear 10 A 0 0 none
47 45 2/45/47 Tops 10 A 0 0 none
93 89 5/89/93 Bedding Sets 20 A 0 0 none
19 3 3/19 Bottom Wear 20 A 0 0 none
68 4 4/68 Bottom Wear 20 A 0 0 none
80 78 78/80 Bottom Wear 20 A 0 0 none
10 8 3/8/10 Casual Shirts 20 A 0 0 none
21 19 3/19/21 Casual Trousers 20 A 0 0 none
98 90 5/90/98 Floor Mats & Dhurries 20 A 0 0 none
90 5 5/90 Flooring 20 A 0 0 none
101 91 5/91/101 Hand & Face Towels 20 A 0 0 none
30 25 3/25/30 Kurta Sets 20 A 0 0 none
105 82 4/82/105 Kurta Sets 20 A 0 0 none
32 36 2/36/32 Kurta Sets 20 A 0 0 none
117 81 78/81/117 Kurta Sets 20 A 0 0 none
70 67 4/67/70 Shirts 20 A 0 0 none
48 45 2/45/48 T-Shirts 20 A 0 0 none
45 2 2/45 Top Wear 20 A 0 0 none
61 46 2/46/61 Trousers 20 A 0 0 none
110 80 78/80/110 Trousers 20 A 0 0 none
84 68 4/68/84 Trousers 20 A 0 0 none
2 0 2 WOMEN 20 A 0 0 none
91 5 5/91 Bath 30 A 0 0 none
94 89 5/89/94 Blankets, Quilts & Dohars 30 A 0 0 none
46 2 2/46 Bottom Wear 30 A 0 0 none
62 46 2/46/62 Capris 30 A 0 0 none
111 80 78/80/111 Capris 30 A 0 0 none
99 90 5/90/99 Door Mats 30 A 0 0 none
25 3 3/25 Ethnic Wear 30 A 0 0 none
81 78 78/81 Ethnic Wear 30 A 0 0 none
82 4 4/82 Ethnic Wear 30 A 0 0 none
11 8 3/8/11 Formal Shirts 30 A 0 0 none
22 19 3/19/22 Formal Trousers 30 A 0 0 none
118 81 78/81/118 Kurtis 30 A 0 0 none
33 36 2/36/33 Kurtis 30 A 0 0 none
3 0 3 MEN 30 A 0 0 none
107 82 4/82/107 Nehru Jackets 30 A 0 0 none
27 25 3/25/27 Sherwanis 30 A 0 0 none
49 45 2/45/49 Shirts 30 A 0 0 none
85 68 4/68/85 Shorts 30 A 0 0 none
71 67 4/67/71 Sweat Shirts 30 A 0 0 none
102 91 5/91/102 Towel Sets 30 A 0 0 none
103 91 5/91/103 Bathroom Accessories 40 A 0 0 none
4 0 4 BOYS 40 A 0 0 none
108 82 4/82/108 Dhotis 40 A 0 0 none
119 81 78/81/119 Leggings 40 A 0 0 none
34 36 2/36/34 Leggings 40 A 0 0 none
28 25 3/25/28 Nehru Jackets 40 A 0 0 none
95 89 5/89/95 Pillows & Pillows Covers 40 A 0 0 none
63 46 2/46/63 Shorts 40 A 0 0 none
23 19 3/19/23 Shorts 40 A 0 0 none
112 80 78/80/112 Shorts 40 A 0 0 none
12 8 3/8/12 Sweat Shirts 40 A 0 0 none
51 45 2/45/51 Sweat Shirts 40 A 0 0 none
86 68 4/68/86 Track Pants 40 A 0 0 none
96 89 5/89/96 Bed Covers 50 A 0 0 none
35 36 2/36/35 Churidars 50 A 0 0 none
29 25 3/25/29 Dhotis 50 A 0 0 none
78 0 78 GIRLS 50 A 0 0 none
73 67 4/67/73 Jackets 50 A 0 0 none
120 81 78/81/120 Palazzos 50 A 0 0 none
50 45 2/45/50 Shrugs 50 A 0 0 none
113 80 78/80/113 Skirts 50 A 0 0 none
64 46 2/46/64 Skirts 50 A 0 0 none
13 8 3/8/13 Sweaters 50 A 0 0 none
24 19 3/19/24 Track Pants 50 A 0 0 none
74 67 4/67/74 Blazers & Coats 60 A 0 0 none
114 80 78/80/114 Dungarees 60 A 0 0 none
65 46 2/46/65 Dungarees 60 A 0 0 none
5 0 5 HOME 60 A 0 0 none
14 8 3/8/14 Jackets 60 A 0 0 none
121 81 78/81/121 Lehenga Cholis 60 A 0 0 none
37 36 2/36/37 Patiala & Salwar 60 A 0 0 none
52 45 2/45/52 Sweaters 60 A 0 0 none
115 80 78/80/115 Track Pants 70 A 0 0 none
15 8 3/8/15 Blazers & Coats 70 A 0 0 none
122 81 78/81/122 Gowns 70 A 0 0 none
53 45 2/45/53 Jackets 70 A 0 0 none
38 36 2/36/38 Palazzos 70 A 0 0 none
75 67 4/67/75 Suits 70 A 0 0 none
66 46 2/46/66 Track Pants 70 A 0 0 none
54 45 2/45/54 Blazers & Coats 80 A 0 0 none
39 36 2/36/39 Dress Materials 80 A 0 0 none
16 8 3/8/16 Suits 80 A 0 0 none
76 67 4/67/76 Track Suits 80 A 0 0 none
77 67 4/67/77 Rain Coats 90 A 0 0 none
41 36 2/36/41 Sarees 90 A 0 0 none
57 45 2/45/57 Suits 90 A 0 0 none
18 8 3/8/18 Track Suits 90 A 0 0 none
59 45 2/45/59 Dresses 100 A 0 0 none
40 36 2/36/40 Lehenga Cholis 100 A 0 0 none
17 8 3/8/17 Rain Coats 100 A 0 0 none
43 36 2/36/43 Gowns 110 A 0 0 none
55 45 2/45/55 Jump Suits 110 A 0 0 none
42 36 2/36/42 Dupattas 120 A 0 0 none
56 45 2/45/56 Track Suits 120 A 0 0 none
58 45 2/45/58 Rain Coats 130 A 0 0 none
44 36 2/36/44 Shawls 130 A 0 0 none