SELECT 
  q_categories.category_id, 
  q_categories.parent_id, 
  q_categories.id_path, 
  q_category_descriptions.category, 
  q_categories.position, 
  q_categories.status, 
  q_seo_names.name as seo_name, 
  q_seo_names.path as seo_path, 
  q_categories.ab__lc_catalog_image_control 
FROM 
  q_categories 
  LEFT JOIN q_category_descriptions ON q_categories.category_id = q_category_descriptions.category_id 
  AND q_category_descriptions.lang_code = 'en' 
  LEFT JOIN q_seo_names ON q_seo_names.object_id = q_categories.category_id 
  AND q_seo_names.type = 'c' 
  AND q_seo_names.dispatch = '' 
  AND q_seo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    q_categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, q_categories.usergroup_ids) 
    OR FIND_IN_SET(1, q_categories.usergroup_ids)
  ) 
  AND q_categories.status IN ('A') 
  AND q_categories.level <= 3 
  AND q_categories.category_id IN(
    2, 
    10, 
    11, 
    12, 
    13, 
    14, 
    44, 
    50, 
    51, 
    58, 
    84, 
    604, 
    91, 
    144, 
    100, 
    101, 
    102, 
    106, 
    113, 
    115, 
    117, 
    121, 
    130, 
    131, 
    132, 
    133, 
    134, 
    135, 
    136, 
    137, 
    138, 
    139, 
    140, 
    142, 
    145, 
    146, 
    6, 
    7, 
    147, 
    153, 
    154, 
    159, 
    164, 
    167, 
    168, 
    169, 
    170, 
    179, 
    181, 
    183, 
    184, 
    185, 
    189, 
    190, 
    193, 
    194, 
    196, 
    197, 
    198, 
    199, 
    201, 
    202, 
    203, 
    245, 
    246, 
    248, 
    249, 
    252, 
    46, 
    294, 
    296, 
    299, 
    301, 
    361, 
    362, 
    364, 
    366, 
    367, 
    368, 
    378, 
    382, 
    383, 
    384, 
    385, 
    386, 
    387, 
    389, 
    395, 
    396, 
    400, 
    401, 
    402, 
    458, 
    403, 
    404, 
    405, 
    412, 
    413, 
    191, 
    419, 
    420, 
    421, 
    422, 
    423, 
    424, 
    425, 
    427, 
    428, 
    429, 
    431, 
    432, 
    438, 
    439, 
    442, 
    445, 
    446, 
    448, 
    450, 
    454, 
    455, 
    457, 
    465, 
    468, 
    207, 
    469, 
    470, 
    473, 
    482, 
    253, 
    489, 
    513, 
    516, 
    523, 
    529, 
    544, 
    545, 
    549, 
    555, 
    560, 
    564, 
    565, 
    578, 
    582, 
    584, 
    586, 
    589, 
    590, 
    591
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.02291

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "146.37"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "q_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 598,
            "rows_produced_per_join": 4,
            "filtered": "0.82",
            "cost_info": {
              "read_cost": "133.54",
              "eval_cost": "0.99",
              "prefix_cost": "134.53",
              "data_read_per_join": "13K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "level",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control"
            ],
            "attached_condition": "(((`portal`.`q_categories`.`usergroup_ids` = '') or find_in_set(0,`portal`.`q_categories`.`usergroup_ids`) or find_in_set(1,`portal`.`q_categories`.`usergroup_ids`)) and (`portal`.`q_categories`.`status` = 'A') and (`portal`.`q_categories`.`level` <= 3) and (`portal`.`q_categories`.`category_id` in (2,10,11,12,13,14,44,50,51,58,84,604,91,144,100,101,102,106,113,115,117,121,130,131,132,133,134,135,136,137,138,139,140,142,145,146,6,7,147,153,154,159,164,167,168,169,170,179,181,183,184,185,189,190,193,194,196,197,198,199,201,202,203,245,246,248,249,252,46,294,296,299,301,361,362,364,366,367,368,378,382,383,384,385,386,387,389,395,396,400,401,402,458,403,404,405,412,413,191,419,420,421,422,423,424,425,427,428,429,431,432,438,439,442,445,446,448,450,454,455,457,465,468,207,469,470,473,482,253,489,513,516,523,529,544,545,549,555,560,564,565,578,582,584,586,589,590,591)))"
          }
        },
        {
          "table": {
            "table_name": "q_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "portal.q_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "4.93",
              "eval_cost": "0.99",
              "prefix_cost": "140.45",
              "data_read_per_join": "14K"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "q_seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "portal.q_categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "4.93",
              "eval_cost": "0.99",
              "prefix_cost": "146.37",
              "data_read_per_join": "8K"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id parent_id id_path category position status seo_name seo_path ab__lc_catalog_image_control
301 0 301 Products 0 A products none
191 190 190/191 Cover Cases 10 A cases 190 none
362 361 248/361/362 Baby Bath 10 A baby-bath 248/361 none
7 6 6/7 Bed & Bath 10 A bed-and-bath 6 none
590 589 2/589/590 Beverages 10 A beverages 2/589 none
169 168 6/168/169 Blenders 10 A blenders 6/168 none
413 412 299/412/413 Camera Accessories 10 A camera-accessories 299/412 none
470 469 207/469/470 Camping & Hiking 10 A camping-and-hiking 207/469 none
446 445 6/445/446 Cleaning Supplies & Tools 10 A cleaning-tools 6/445 none
14 13 12/13/14 Clothing 10 A women-clothing 12/13 none
51 50 12/50/51 Clothing 10 A men-clothing 12/50 none
396 395 248/395/396 Diapers & Wipes 10 A diapers-and-wipes 248/395 none
439 438 6/438/439 Dining & Entertaining 10 A dining-and-entertaining 6/438 none
368 367 6/367/368 Dogs 10 A dogs 6/367 none
134 10 2/10/134 Face 10 A face 2/10 none
423 422 299/422/423 Gaming Accessories 10 A gaming-accessories 299/422 none
2 0 2 Health & Beauty 10 A beauty none
185 184 6/184/185 Power Tools 10 A power-tools 6/184 none
130 11 2/11/130 Shampoo 10 A shampoo 2/11 none
145 144 2/144/145 Shave & Hair Cutting 10 A shave-and-hair-cutting 2/144 none
382 299 299/382 Speakers 10 A speakers 299 none
545 544 248/544/545 Stationery 10 A stationery 248/544 none
139 138 2/138/139 Toothbrushes 10 A toothbrushes 2/138 none
249 248 248/249 Toys 10 A toys 248 none
564 555 12/555/564 Travel Accessories 10 A travel-accessories 12/555 none
384 383 299/383/384 USB Led Light 10 A usb-led-light 299/383 none
246 245 2/245/246 Vitamins 10 A vitamins 2/245 none
13 12 12/13 Womens Fashion 10 A womens-fashion 12 none
146 144 2/144/146 Accessories & Tools 20 A acc-and-tools 2/144 none
135 10 2/10/135 Body 20 A body 2/10 none
402 383 299/383/402 Cables, Adapters & Connectors 20 A cables-adapters-and-connectors 299/383 none
383 299 299/383 Computer & Laptop Accessories 20 A computer-and-laptop-accessories 299 none
131 11 2/11/131 Conditioner 20 A conditioner 2/11 none
159 6 6/159 Decor 20 A decor 6 none
6 0 6 Home & Decor 20 A home-and-decor none
170 168 6/168/170 Juicers 20 A juicers 6/168 none
100 144 2/144/100 Make-Up 20 A make-up 2/144 none
50 12 12/50 Mens Fashion 20 A mens-fashion 12 none
427 425 6/425/427 Smoking Accessories 20 A smoking-accessories 6/425 none
419 191 190/191/419 Tablet Cover Case 20 A tablet-cover-case 190/191 none
591 589 2/589/591 Tea & Coffee 20 A tea-and-coffee 2/589 none
431 299 299/431 Television & Video 20 A television-video 299 none
140 138 2/138/140 Toothpaste 20 A toothpaste 2/138 none
193 190 190/193 Chargers & Power Adapters 30 A chargers-and-power-adapters 190 none
147 7 6/7/147 Bathroom 30 A bathroom 6/7 none
252 249 248/249/252 Building & Blocks 30 A building-and-blocks 248/249 none
366 144 2/144/366 Family Planning & Contraceptives 30 A family-planning-and-contraceptives 2/144 none
12 0 12 Fashion 30 A fashion none
136 10 2/10/136 Feet 30 A feet 2/10 none
516 513 207/513/516 Gloves 30 A gloves 207/513 none
132 11 2/11/132 Hair coloring products 30 A hair-coloring-products 2/11 none
364 361 248/361/364 Health & Baby Care 30 A health-and-baby-care 248/361 none
454 438 6/438/454 Kitchen Utensils & Gadgets 30 A kitchen-gadgets 6/438 none
385 299 299/385 Routers 30 A routers 299 none
10 2 2/10 Skin Care 30 A skin-care 2 none
168 6 6/168 Small Appliances 30 A small-appliances 6 none
91 604 12/604/91 Sunglasses 30 A sunglasses 12/604 none
194 190 190/194 Batteries & Power Banks 40 A batteries-and-powerbanks 190 none
44 13 12/13/44 Accessories 40 A women-accessories 12/13 none
84 50 12/50/84 Accessories 40 A accessories 12/50 none
400 144 2/144/400 Deodorants & Antiperspirants 40 A deodorants-and-antiperspirants 2/144 none
11 2 2/11 Hair Care 40 A hair-care 2 none
133 11 2/11/133 Hair Treatment Products 40 A hair-treatment-products 2/11 none
137 10 2/10/137 Hands 40 A hands 2/10 none
560 383 299/383/560 Keyboard & Mouse 40 A keyboard-mouse 299/383 none
190 0 190 Mobiles & Tablets 40 A mobiles-tablets none
142 138 2/138/142 Mouthwash 40 A mouthwash 2/138 none
253 249 248/249/253 Puzzles & Boardgames 40 A puzzles-and-boardgames 248/249 none
387 299 299/387 Scales 40 A scales 299 none
189 184 6/184/189 Storage & Home Organization 40 A storage-and-home-organization 6/184 none
184 6 6/184 Tools & Home Improvement 40 A tools-and-home 6 none
555 12 12/555 Bags & Luggage 50 A bags-and-luggage 12 none
523 11 2/11/523 Hair Styling Tools 50 A hair-styling-tools 2/11 none
164 159 6/159/164 Mirrors 50 A mirrors 6/159 none
138 2 2/138 Oral Care 50 A oral-care 2 none
207 0 207 Sport & Fitness 50 A sport-fitness none
378 184 6/184/378 Water & Air Purifiers Filters 50 A water-and-air-purifiers-and-filters 6/184 none
196 190 190/196 Selfie Sticks & Tripods 60 A selfie-sticks-and-tripods 190 none
361 248 248/361 Bath & Baby Care 60 A bath-and-baby-care 248 none
565 412 299/412/565 Drone & Accessories 60 A drone-and-accessories 299/412 none
458 144 2/144/458 Feminine Care 60 A feminine-care 2/144 none
248 0 248 Kids Corner 60 A kids-corner none
144 2 2/144 Personal Care 60 A personalcare 2 none
389 299 299/389 Security Cameras 60 A security-cameras 299 none
582 12 12/582 Sewing Supplies 60 A sewing-supplies 12 none
197 190 190/197 Mobile Game Controllers 70 A mobile-game-controllers 190 none
299 0 299 Electronics 70 A electronics none
604 12 12/604 Eyewear 70 A eyewear 12 none
549 144 2/144/549 Massage & Relaxation 70 A massage-and-relaxation 2/144 none
401 299 299/401 Recording & Studio Equipment 70 A recording-and-studio-equipment 299 none
465 184 6/184/465 Safety & Security 70 A safety-and-security 6/184 none
245 2 2/245 Supplements & Nutrition 70 A supplements-and-vitamins 2 none
198 190 190/198 Virtual Reality Headsets 80 A virtual-reality-headsets 190 none
412 299 299/412 Camera, Photo & Video 80 A camera-photo-and-video 299 none
395 248 248/395 Diapering 80 A diapering 248 none
167 159 6/159/167 Lighting 80 A lighting 6/159 none
425 6 6/425 Outdoor 80 A outdoor 6 none
405 2 2/405 Sleep & Snoring 80 A sleep-and-snoring 2 none
199 190 190/199 Car Accessories 90 A car-accessories 190 none
438 6 6/438 Kitchen & Dining 90 A kitchen-dining 6 none
432 2 2/432 Monitoring & Testing Equipment 90 A monitoring-and-testing-equipment 2 none
421 299 299/421 Music & Karaoke 90 A music-and-karaoke 299 none
544 248 248/544 School & Stationery 90 A school-and-stationery 248 none
445 6 6/445 Household Supplies 100 A house-supplies 6 none
422 299 299/422 Video Games & Accessories 100 A video-games-accessories 299 none
201 190 190/201 Bluetooth Headsets 110 A bluetooth-headsets 190 none
179 168 6/168/179 Fans 110 A fans 6/168 none
578 2 2/578 Medication & Remedies 110 A medication-and-remedies 2 none
202 190 190/202 Cables & Connectors 120 A selfie-sticks-and-tripods-en 190 none
589 2 2/589 Food & Beverages 120 A f-and-b 2 none
203 190 190/203 Memory Cards 130 A memory-cards 190 none
181 168 6/168/181 Iron & Steamers 130 A iron-and-steamers 6/168 none
367 6 6/367 Pet Supplies 130 A pet-supplies 6 none
183 168 6/168/183 Kitchen Tools & Accessories 150 A kitchen-tools-and-accessories 6/168 none
386 190 190/386 Headset 160 A headset 190 none
420 190 190/420 Keyboard 170 A keyboard 190 none
424 190 190/424 Bluetooth Speakers 180 A bluetooth-speakers 190 none
469 207 207/469 Outdoor Recreation 180 A outdoor-recreation 207 none
513 207 207/513 Exercise & Fitness Accessories 190 A exercise-and-fitness-accessories 207 none
428 190 190/428 Mobile Holders & Stands 190 A mobile-holders-and-stands 190 none
429 190 190/429 Mobile Tools & Accessories 200 A mobile-tools-and-accessories 190 none
529 168 6/168/529 Insect Killer 220 A insect-killer 6/168 none