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(
    6, 
    12, 
    13, 
    14, 
    15, 
    35, 
    38, 
    42, 
    44, 
    46, 
    47, 
    48, 
    50, 
    51, 
    60, 
    7, 
    61, 
    63, 
    64, 
    68, 
    77, 
    79, 
    82, 
    83, 
    84, 
    88, 
    89, 
    604, 
    91, 
    95, 
    96, 
    97, 
    98, 
    99, 
    2, 
    144, 
    100, 
    113, 
    116, 
    117, 
    10, 
    136, 
    145, 
    147, 
    148, 
    151, 
    153, 
    154, 
    159, 
    160, 
    164, 
    166, 
    167, 
    168, 
    179, 
    180, 
    183, 
    184, 
    185, 
    186, 
    187, 
    189, 
    190, 
    191, 
    192, 
    193, 
    194, 
    195, 
    196, 
    197, 
    199, 
    200, 
    201, 
    202, 
    204, 
    206, 
    207, 
    217, 
    221, 
    224, 
    225, 
    241, 
    242, 
    245, 
    248, 
    249, 
    250, 
    251, 
    252, 
    253, 
    254, 
    255, 
    256, 
    257, 
    258, 
    260, 
    263, 
    264, 
    273, 
    274, 
    277, 
    278, 
    289, 
    290, 
    294, 
    296, 
    298, 
    299, 
    300, 
    301, 
    302, 
    303, 
    315, 
    330, 
    360, 
    361, 
    362, 
    364, 
    367, 
    368, 
    369, 
    370, 
    371, 
    375, 
    376, 
    377, 
    379, 
    382, 
    383, 
    384, 
    385, 
    386, 
    389, 
    393, 
    395, 
    400, 
    401, 
    402, 
    406, 
    410, 
    412, 
    413, 
    418, 
    419, 
    420, 
    421, 
    422, 
    424, 
    425, 
    426, 
    428, 
    429, 
    431, 
    433, 
    434, 
    435, 
    436, 
    438, 
    439, 
    442, 
    443, 
    444, 
    445, 
    446, 
    450, 
    454, 
    455, 
    459, 
    460, 
    465, 
    467, 
    468, 
    469, 
    470, 
    473, 
    484, 
    489, 
    493, 
    495, 
    496, 
    497, 
    498, 
    499, 
    508, 
    509, 
    512, 
    513, 
    514, 
    515, 
    516, 
    11, 
    523, 
    529, 
    519, 
    530, 
    531, 
    532, 
    533, 
    535, 
    536, 
    539, 
    543, 
    544, 
    545, 
    546, 
    548, 
    550, 
    551, 
    552, 
    554, 
    555, 
    556, 
    557, 
    558, 
    559, 
    560, 
    561, 
    562, 
    563, 
    564, 
    565, 
    566, 
    569, 
    570, 
    571, 
    572, 
    573, 
    576, 
    578, 
    577, 
    579, 
    580, 
    581, 
    582, 
    583, 
    584, 
    585, 
    135, 
    586, 
    587, 
    589, 
    592, 
    593, 
    596, 
    597, 
    598, 
    602, 
    606
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00510

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "154.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": 8,
            "filtered": "1.38",
            "cost_info": {
              "read_cost": "132.88",
              "eval_cost": "1.65",
              "prefix_cost": "134.53",
              "data_read_per_join": "21K"
            },
            "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 (6,12,13,14,15,35,38,42,44,46,47,48,50,51,60,7,61,63,64,68,77,79,82,83,84,88,89,604,91,95,96,97,98,99,2,144,100,113,116,117,10,136,145,147,148,151,153,154,159,160,164,166,167,168,179,180,183,184,185,186,187,189,190,191,192,193,194,195,196,197,199,200,201,202,204,206,207,217,221,224,225,241,242,245,248,249,250,251,252,253,254,255,256,257,258,260,263,264,273,274,277,278,289,290,294,296,298,299,300,301,302,303,315,330,360,361,362,364,367,368,369,370,371,375,376,377,379,382,383,384,385,386,389,393,395,400,401,402,406,410,412,413,418,419,420,421,422,424,425,426,428,429,431,433,434,435,436,438,439,442,443,444,445,446,450,454,455,459,460,465,467,468,469,470,473,484,489,493,495,496,497,498,499,508,509,512,513,514,515,516,11,523,529,519,530,531,532,533,535,536,539,543,544,545,546,548,550,551,552,554,555,556,557,558,559,560,561,562,563,564,565,566,569,570,571,572,573,576,578,577,579,580,581,582,583,584,585,135,586,587,589,592,593,596,597,598,602,606)))"
          }
        },
        {
          "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": 8,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "8.27",
              "eval_cost": "1.65",
              "prefix_cost": "144.45",
              "data_read_per_join": "25K"
            },
            "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": 8,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "8.27",
              "eval_cost": "1.65",
              "prefix_cost": "154.37",
              "data_read_per_join": "14K"
            },
            "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
543 190 190/543 Mobile Phones 5 A mobiles 190 none
551 190 190/551 Tablets 5 A tablets 190 none
191 190 190/191 Cover Cases 10 A cases 190 none
514 513 207/513/514 Exercise Balls 10 A exercise-balls 207/513 none
160 159 6/159/160 Wall Stickers & Murals 10 A wall-stickers-and-murals 6/159 none
602 375 300/375/602 Automotive Keychains 10 A automotive-keychains 300/375 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
258 257 248/257/258 Bikes & Ride-Ons 10 A bikes-and-ride-ons 248/257 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
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
250 249 248/249/250 Dolls & Accessories 10 A dolls-and-accessories 248/249 none
303 302 248/302/303 Girls 10 A girls 248/302 none
2 0 2 Health & Beauty 10 A beauty none
375 300 300/375 Interior Accessories 10 A interior-accessories 300 none
274 273 248/273/274 Masks, Props & Accessories 10 A masks-props-and-accessories 248/273 none
418 191 190/191/418 Mobile Cover Case 10 A mobile-cover-case 190/191 none
576 401 299/401/576 Musical Instruments & Accessories 10 A musical-instruments-and-accessories 299/401 none
426 425 6/425/426 Outdoor Grills & Accessories 10 A outdoor-grills-and-accessories 6/425 none
185 184 6/184/185 Power Tools 10 A power-tools 6/184 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
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
96 95 12/95/96 Women 10 A women-watches 12/95 none
13 12 12/13 Womens Fashion 10 A womens-fashion 12 none
192 190 190/192 Screen Protectors 20 A screen-protectors 190 none
61 7 6/7/61 Bedding 20 A bedding 6/7 none
135 10 2/10/135 Body 20 A body 2/10 none
330 302 248/302/330 Boys 20 A boys 248/302 none
402 383 299/383/402 Cables, Adapters & Connectors 20 A cables-adapters-and-connectors 299/383 none
251 249 248/249/251 Cars & Trucks 20 A cars-and-trucks 248/249 none
369 367 6/367/369 Cats 20 A cats 6/367 none
383 299 299/383 Computer & Laptop Accessories 20 A computer-and-laptop-accessories 299 none
443 438 6/438/443 Cutlery & Knife Accessories 20 A cutlery-and-knife-accessories 6/438 none
159 6 6/159 Decor 20 A decor 6 none
515 513 207/513/515 Exercise Mats 20 A exercise-mats 207/513 none
376 300 300/376 Exterior Accessories 20 A exterior-accessories 300 none
493 422 299/422/493 Games 20 A games 299/422 none
264 257 248/257/264 Garden & Pool 20 A garden-pool 248/257 none
186 184 6/184/186 Hand Tools 20 A hand-tools 6/184 none
6 0 6 Home & Decor 20 A home-and-decor none
100 144 2/144/100 Make-Up 20 A make-up 2/144 none
97 95 12/95/97 Men 20 A men-watches 12/95 none
50 12 12/50 Mens Fashion 20 A mens-fashion 12 none
257 248 248/257 Outdoor Play 20 A outdoor-play 248 none
546 544 248/544/546 School Bags 20 A school-bags 248/544 none
15 13 12/13/15 Shoes 20 A women-shoes 12/13 none
68 50 12/50/68 Shoes 20 A shoes 12/50 none
217 207 207/217 Swimming 20 A swimming 207 none
419 191 190/191/419 Tablet Cover Case 20 A tablet-cover-case 190/191 none
431 299 299/431 Television & Video 20 A television-video 299 none
193 190 190/193 Chargers & Power Adapters 30 A chargers-and-power-adapters 190 none
38 13 12/13/38 Bags 30 A women-bags 12/13 none
77 50 12/50/77 Bags 30 A bags 12/50 none
147 7 6/7/147 Bathroom 30 A bathroom 6/7 none
592 589 2/589/592 Breads & Bakery 30 A breads-and-bakery 2/589 none
252 249 248/249/252 Building & Blocks 30 A building-and-blocks 248/249 none
12 0 12 Fashion 30 A fashion none
136 10 2/10/136 Feet 30 A feet 2/10 none
370 367 6/367/370 Fish & Aquatic Pets 30 A fish-and-aquatic-pets 6/367 none
187 184 6/184/187 Garden Tools 30 A garden-tools 6/184 none
516 513 207/513/516 Gloves 30 A gloves 207/513 none
364 361 248/361/364 Health & Baby Care 30 A health-and-baby-care 248/361 none
530 519 6/519/530 Heating, Cooling & Air Quality 30 A heating-cooling-and-air-quality 6/519 none
98 95 12/95/98 Kids 30 A kids-watches 12/95 none
454 438 6/438/454 Kitchen Utensils & Gadgets 30 A kitchen-gadgets 6/438 none
512 383 299/383/512 Laptop Bags 30 A laptop-bags 299/383 none
377 300 300/377 Lighting & Equipments 30 A lighting-and-equipments 300 none
273 248 248/273 Party Time 30 A party-time 248 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
84 50 12/50/84 Accessories 40 A accessories 12/50 none
44 13 12/13/44 Accessories 40 A women-accessories 12/13 none
371 367 6/367/371 Birds 40 A birds 6/367 none
593 589 2/589/593 Canned, Dry & Packaged Food 40 A canned-food 2/589 none
289 278 248/278/289 Carriers & Slings 40 A carriers-and-slings 248/278 none
460 438 6/438/460 Cookware 40 A cookware 6/438 none
277 273 248/273/277 Costumes 40 A costumes 248/273 none
400 144 2/144/400 Deodorants & Antiperspirants 40 A deodorants-and-antiperspirants 2/144 none
278 248 248/278 Gear 40 A gear 248 none
11 2 2/11 Hair Care 40 A hair-care 2 none
560 383 299/383/560 Keyboard & Mouse 40 A keyboard-mouse 299/383 none
548 544 248/544/548 Kids Water Bottles 40 A kids-water-bottles 248/544 none
190 0 190 Mobiles & Tablets 40 A mobiles-tablets none
253 249 248/249/253 Puzzles & Boardgames 40 A puzzles-and-boardgames 248/249 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
597 95 12/95/597 Watch Accessories 40 A watch-accessories 12/95 none
95 12 12/95 Watches 40 A watches 12 none
195 190 190/195 Mounts & Stands 50 A mounts-and-stands 190 none
290 278 248/278/290 Bags 50 A bags 248/278 none
555 12 12/555 Bags & Luggage 50 A bags-and-luggage 12 none
410 300 300/410 Car Care 50 A car-care 300 none
523 11 2/11/523 Hair Styling Tools 50 A hair-styling-tools 2/11 none
302 248 248/302 Kids Fashion 50 A kids-fashion 248 none
561 383 299/383/561 Laptop Cover Case 50 A laptop-cover-case 299/383 none
164 159 6/159/164 Mirrors 50 A mirrors 6/159 none
207 0 207 Sport & Fitness 50 A sport-fitness none
254 249 248/249/254 Stuffed Animals & Plush 50 A stuffed-animals-and-plush 248/249 none
196 190 190/196 Selfie Sticks & Tripods 60 A selfie-sticks-and-tripods 190 none
255 249 248/249/255 Arts & Crafts 60 A arts-and-crafts 248/249 none
361 248 248/361 Bath & Baby Care 60 A bath-and-baby-care 248 none
221 207 207/221 Cycling 60 A cycling 207 none
565 412 299/412/565 Drone & Accessories 60 A drone-and-accessories 299/412 none
571 406 6/406/571 Furniture Colors & Polish 60 A furniture-colors-and-polish 6/406 none
248 0 248 Kids Corner 60 A kids-corner none
144 2 2/144 Personal Care 60 A personalcare 2 none
562 383 299/383/562 Screen Protector 60 A screen-protector 299/383 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
360 6 6/360 Table Covers 60 A table-covers 6 none
166 159 6/159/166 Decorative Accessories 70 A decorative-accessories 6/159 none
197 190 190/197 Mobile Game Controllers 70 A mobile-game-controllers 190 none
587 438 6/438/587 Baking Supplies & Tools 70 A baking-supplies-and-tools 6/438 none
256 249 248/249/256 Educational 70 A educational 248/249 none
299 0 299 Electronics 70 A electronics none
393 248 248/393 Electronics 70 A electronics 248 none
604 12 12/604 Eyewear 70 A eyewear 12 none
406 6 6/406 Furniture 70 A furniture 6 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
300 0 300 Automotive 80 A automotive 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
536 184 6/184/536 Hardware 80 A hardware 6/184 none
167 159 6/159/167 Lighting 80 A lighting 6/159 none
425 6 6/425 Outdoor 80 A outdoor 6 none
572 300 300/572 Scooter & Bikes Accessories 80 A scooter-and-bikes-accessories 300 none
585 144 2/144/585 Tattoo & Piercing Supplies 80 A tattoo-and-piercing-supplies 2/144 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
495 159 6/159/495 Mats 90 A mats 6/159 none
433 249 248/249/433 Money Banks 90 A money-banks 248/249 none
421 299 299/421 Music & Karaoke 90 A music-and-karaoke 299 none
573 300 300/573 Oils & Fluids 90 A oils-and-fluids 300 none
558 184 6/184/558 Paint 90 A paint 6/184 none
544 248 248/544 School & Stationery 90 A school-and-stationery 248 none
224 207 207/224 Tennis 90 A tennis 207 none
200 190 190/200 Corded Headsets 100 A corded-headsets 190 none
566 248 248/566 Baby Feeding 100 A baby-feeding 248 none
563 159 6/159/563 Clocks 100 A clocks 6/159 none
577 578 2/578/577 First Aid 100 A first-aid 2/578 none
434 249 248/249/434 Hobby 100 A hobby 248/249 none
445 6 6/445 Household Supplies 100 A house-supplies 6 none
225 207 207/225 Squash 100 A squash 207 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
459 249 248/249/459 Action & Toy Figures 110 A action-and-toy-figures 248/249 none
179 168 6/168/179 Fans 110 A fans 6/168 none
570 159 6/159/570 Home Fragrance 110 A home-fragrance 6/159 none
569 248 248/569 Kids & Baby Furniture 110 A kids-and-baby-furniture 248 none
519 6 6/519 Large Appliances 110 A large-appliances 6 none
578 2 2/578 Medication & Remedies 110 A medication-and-remedies 2 none
550 299 299/550 Projectors 110 A projectors 299 none
202 190 190/202 Cables & Connectors 120 A selfie-sticks-and-tripods-en 190 none
596 6 6/596 Event & Party Supplies 120 A event-and-party-supplies 6 none
589 2 2/589 Food & Beverages 120 A f-and-b 2 none
598 248 248/598 Nursery Bedding & Mattresses 120 A nursery-bedding-and-mattresses 248 none
552 299 299/552 Telephones & Accessories 120 A telephones-and-accessories 299 none
581 300 300/581 Tools & Equipment 120 A tools-and-equipment 300 none
180 168 6/168/180 Vacuum Cleaner 120 A vacuum-cleaner 6/168 none
554 299 299/554 GPS & Tracker 130 A gps-and-tracker 299 none
367 6 6/367 Pet Supplies 130 A pet-supplies 6 none
484 249 248/249/484 Toy Balls 130 A toy-balls-en 248/249 none
204 190 190/204 Smart Watch Accessories 140 A smart-watch-accessories 190 none
606 300 300/606 Automotive Electronics 140 A automotive-electronics 300 none
557 299 299/557 Laptops 140 A laptops 299 none
241 207 207/241 Outdoor 140 A outdoor 207 none
533 249 248/249/533 Remote Control Toys 140 A remote-control-toys 248/249 none
535 249 248/249/535 Drones 150 A drones 248/249 none
242 207 207/242 Fishing 150 A fishing 207 none
183 168 6/168/183 Kitchen Tools & Accessories 150 A kitchen-tools-and-accessories 6/168 none
559 299 299/559 Printers & Accessories 150 A printers-and-accessories 299 none
206 190 190/206 Smart Watches 150 A smart-watches 190 none
579 249 248/249/579 Baby & Toddler Toys 160 A baby-and-toddler-toys 248/249 none
386 190 190/386 Headset 160 A headset 190 none
379 207 207/379 Fitness Technology 170 A fitness-technology 207 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
580 207 207/580 Badminton 200 A badminton 207 none
429 190 190/429 Mobile Tools & Accessories 200 A mobile-tools-and-accessories 190 none
556 190 190/556 Tablet Accessories 210 A tablet-accessories 190 none
529 168 6/168/529 Insect Killer 220 A insect-killer 6/168 none
531 168 6/168/531 Specialty Appliances 230 A specialty-appliances 6/168 none