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 = 'uk' 
  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.category_id != 264 
  AND cscart_categories.parent_id != 264 
  AND cscart_categories.level <= 3 
  AND cscart_categories.storefront_id IN (0, 1) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00146

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.29459219,
    "filesort": {
      "sort_key": "cscart_categories.is_trash, cscart_categories.position, cscart_category_descriptions.category",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY", "c_status", "parent", "p_category_id"],
              "loops": 1,
              "rows": 99,
              "cost": 0.0269918,
              "filtered": 100,
              "attached_condition": "(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` = 'A' and cscart_categories.category_id <> 264 and cscart_categories.parent_id <> 264 and cscart_categories.`level` <= 3 and cscart_categories.storefront_id in (0,1)"
            }
          },
          {
            "table": {
              "table_name": "cscart_category_descriptions",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "9",
              "used_key_parts": ["category_id", "lang_code"],
              "ref": ["u985510652_ecartify.cscart_categories.category_id", "const"],
              "loops": 99,
              "rows": 1,
              "cost": 0.09100716,
              "filtered": 100,
              "attached_condition": "trigcond(cscart_category_descriptions.lang_code = 'uk')"
            }
          },
          {
            "table": {
              "table_name": "cscart_seo_names",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "dispatch"],
              "key": "PRIMARY",
              "key_length": "206",
              "used_key_parts": ["object_id", "type", "dispatch", "lang_code"],
              "ref": [
                "u985510652_ecartify.cscart_categories.category_id",
                "const",
                "const",
                "const"
              ],
              "loops": 99,
              "rows": 1,
              "cost": 0.17659323,
              "filtered": 100,
              "attached_condition": "trigcond(cscart_seo_names.`type` = 'c' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
            }
          }
        ]
      }
    }
  }
}

Result

category_id parent_id id_path category position status company_id storefront_id seo_name seo_path
288 287 287/288 0 A 0 0 women 287
289 288 287/288/289 0 A 0 0 dresses 287/288
304 287 287/304 0 A 0 0 men 287
305 304 287/304/305 0 A 0 0 t-shirts 287/304
478 166 166/478 0 A 0 0 phones-en 166
495 219 219/495 0 A 0 0 fiction 219
498 219 219/498 0 A 0 0 non-fiction 219
500 203 203/500 0 A 0 0 fitness 203
501 0 501 0 A 0 0 beauty-and-health
530 0 530 0 A 0 0 sagar
531 530 530/531 0 A 0 0 gaurav 530
532 531 530/531/532 0 A 0 0 abhishek 530/531
533 530 530/533 0 A 0 0 gaurav1 530
534 533 530/533/534 0 A 0 0 abhishek1 530/533
535 0 535 0 A 0 0 test11
536 535 535/536 0 A 0 0 test22 535
537 536 535/536/537 0 A 0 0 test33 535/536
454 0 454 1 A 0 0 beauty-and-personal-care
455 454 454/455 1 A 0 0 make-up 454
456 455 454/455/456 1 A 0 0 eyes 454/455
460 0 460 2 A 0 0 toys-and-games
269 0 269 3 A 0 0 arts-and-crafts
287 0 287 4 A 0 0 fashion
166 0 166 5 A 0 0 best-electronics-tech
223 0 223 6 A 0 0 apparel
204 203 203/204 10 A 0 0 bikes 203
168 167 167/168 10 A 0 0 desktops 167
448 304 287/304/448 10 A 0 0 jeans 287/304
210 204 203/204/210 10 A 0 0 comfort-and-cruisers 203/204
212 211 203/211/212 10 A 0 0 golf-clubs 203/211
461 460 460/461 10 A 0 0 action-and-toy-figures 460
176 175 166/175/176 10 A 0 0 gps-and-navigation 166/175
178 177 166/177/178 10 A 0 0 ipods 166/177
222 219 219/222 10 A 0 0 business-and-investing 219
224 223 223/224 10 A 0 0 mens-clothing 223
229 228 228/229 10 A 0 0 blues 228
190 174 166/174/190 10 A 0 0 led-tvs 166/174
242 241 241/242 10 A 0 0 blu-ray-discs 241
198 196 166/196/198 10 A 0 0 digital-cameras 166/196
270 269 269/270 10 A 0 0 painting-drawing-and-art-supplies 269
271 270 269/270/271 10 A 0 0 beading-and-jewelry-making 269/270
209 204 203/204/209 20 A 0 0 road-bikes 203/204
169 167 167/169 20 A 0 0 laptops 167
211 203 203/211 20 A 0 0 golf 203
213 211 203/211/213 20 A 0 0 golf-balls 203/211
175 166 166/175 20 A 0 0 car-electronics 166
475 460 460/475 20 A 0 0 puzzles-en 460
217 215 203/215/217 20 A 0 0 sleeping-bags 203/215
220 219 219/220 20 A 0 0 computing-and-internet 219
182 177 166/177/182 20 A 0 0 android 166/177
225 223 223/225 20 A 0 0 womens-clothing 223
185 175 166/175/185 20 A 0 0 in-dash-stereos 166/175
230 228 228/230 20 A 0 0 classical 228
191 174 166/174/191 20 A 0 0 plasma-tvs 166/174
243 241 241/243 20 A 0 0 movies-dvd 241
197 196 166/196/197 20 A 0 0 dslr-cameras 166/196
208 204 203/204/208 30 A 0 0 mountain-bikes 203/204
174 166 166/174 30 A 0 0 tv-and-video 166
214 211 203/211/214 30 A 0 0 golf-bags-and-carts 203/211
215 203 203/215 30 A 0 0 camping 203
218 215 203/215/218 30 A 0 0 tents 203/215
179 177 166/177/179 30 A 0 0 mp3-players 166/177
221 219 219/221 30 A 0 0 education 219
226 223 223/226 30 A 0 0 shoes 223
186 175 166/175/186 30 A 0 0 speakers 166/175
231 228 228/231 30 A 0 0 jazz 228
193 174 166/174/193 30 A 0 0 3d-tvs 166/174
244 241 241/244 30 A 0 0 tv-shows-dvd 241
199 196 166/196/199 30 A 0 0 camcorders 166/196
200 196 166/196/200 40 A 0 0 lenses 166/196
202 175 166/175/202 40 A 0 0 subwoofers 166/175
180 177 166/177/180 40 A 0 0 mp3-speaker-systems 166/177
227 223 223/227 40 A 0 0 watches-and-jewelry 223
232 228 228/232 40 A 0 0 rock 228
194 174 166/174/194 40 A 0 0 dvd-and-blu-ray-players 166/174
177 166 166/177 50 A 0 0 mp3-players 166
181 177 166/177/181 50 A 0 0 headphones 166/177
187 175 166/175/187 50 A 0 0 amplifiers 166/175
195 174 166/174/195 50 A 0 0 home-theater-systems 166/174
188 175 166/175/188 60 A 0 0 car-dvd-and-video 166/175
196 166 166/196 60 A 0 0 cameras-and-photo 166
479 166 166/479 80 A 0 0 helmet 166
165 167 167/165 100 A 0 0 tablets 167
170 167 167/170 110 A 0 0 monitors 167
171 167 167/171 120 A 0 0 networking 167
172 167 167/172 130 A 0 0 printers-and-scanners 167
201 167 167/201 140 A 0 0 processors 167
481 0 481 140 A 0 0 washing-machine
526 0 526 210 A 0 0 hourly-product
527 0 527 220 A 0 0 guitars
528 0 528 230 A 0 0 yoga
529 0 529 240 A 0 0 indoor-lighting